How to join results from 2 tables based on no condition
I have 2 tables, say TableA
and TableB
, defined like this:
create table TableA (id int, name varchar (20), partNumber varchar (30));
with values:
insert into TableA values(1,'a1','10');
insert into TableA values(2,'a2','20');
insert into TableA values(3,'a3','30');
insert into TableA values(4,'a4','40');
create table TableB (id int, name varchar (20), partNumber VARCHAR (30));
insert into TableB values(5,'b1','10');
insert into TableB values(6,'b2','20');
insert into TableB values(7,'b3','60');
insert into TableB values(8,'b4','70');
Now, as a result, I want to join both tables and get all records from TableB
and only those records from TableA
where the column value TableA partNumber
doesn't match any value TableB partNumber
.
I tried the following queries and they all gave the same result from 14 entries, which is not correct.
Request 1:
select b.id as Bid, b.name as Bname, b.partNumber as BPart, a.id as Aid, a.name as Aname, a.partNumber as APart from TableB b left join Table A a by a.partNumber! = B.partNumber;
Request 2:
select b.id as Bid, b.name as Bname, b.partNumber as BPart, a.id as Aid, a.name as Aname, a.partNumber as APart from TableB b, TableA a where a.partNumber! = b. partNumber;
Query 3:
select b.id as Bid, b.name as Bname, b.partNumber as BPart, a.id as Aid, a.name as Aname, a.partNumber as APart from TableB b left join Table A a to a.partNumber not in (select a.id as Aid from TableB b join Table A a by a.partNumber = b.partNumber);
Can someone please help me where I am making the mistake here? what is the correct way to get the results.
I expect the output to be like this:
+------+-------+-------+------+-------+-------+
| Bid | Bname | BPart | Aid | Aname | APart |
+------+-------+-------+------+-------+-------+
| 5 | b1 | 10 | 3 | a3 | 30 |
| 5 | b1 | 10 | 4 | a4 | 40 |
| 6 | b2 | 20 | 3 | a3 | 30 |
| 6 | b2 | 20 | 4 | a4 | 40 |
| 7 | b3 | 60 | 3 | a3 | 30 |
| 7 | b3 | 60 | 4 | a4 | 40 |
| 8 | b4 | 70 | 3 | a3 | 30 |
| 8 | b4 | 70 | 4 | a4 | 40 |
+------+-------+-------+------+-------+-------+
So, here's what I mean: As a result, I don't want the record TableA
where partNumber
is 10, 20
, because the values are present in TableB partNumber
.
source to share
You can use the following query:
SELECT id, name, partNumber, Aid, Aname, Apart
FROM TableB AS t
CROSS JOIN (SELECT id AS Aid, name AS Aname, partNumber AS Apart
FROM TableA AS a
WHERE NOT EXISTS (SELECT 1
FROM TableB AS b
WHERE b.partNumber = a.partNumber)) AS c
ORDER BY id
The idea is to select all the required records from TableA
using a suggestion NOT EXISTS
. Then use the CROSS JOIN
resulting table from this query Table1
to get all possible combinations.
source to share