如何基于非条件联接2个表的结果 [英] How to join results of 2 tables based on not condition
问题描述
我有2张桌子，例如TableA& TableB定义如下:
I have 2 tables, say TableA & TableB defined like this:
创建表TableA(id int，名称varchar(20)，partNumber varchar(30));
create table TableA(id int, name varchar(20), partNumber varchar(30));
的值为:
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');
创建表TableB(id int，名称varchar(20)，partNumber varchar(30));
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');
现在，我想要联接两个表，并从TableB获取所有记录，并且仅从TableA获得其中TableA's partNumber列值与TableB's partNumber的任何值都不匹配的那些记录.
Now in the result I want to join both the tables and get all the records from TableB and only those records from TableA where the TableA's partNumber column value that is not matching with any value of TableB's partNumber.
我尝试了以下查询，所有查询都给出了14条记录的相同结果，这是不正确的.
I have tried below queries and all of them gave same result of 14 records which is not correct.
查询1:
选择b.id作为Bid，b.name作为Bname，b.partNumber作为BPart，a.id作为 Aid，来自TableB的a.name为Aname，a.partNumber为APart b左连接 Tablea a在a.partNumber！= b.partNumber;
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 TableA a on a.partNumber!=b.partNumber;
查询2:
选择b.id作为Bid，b.name作为Bname，b.partNumber作为BPart，a.id作为 Aid，a.name作为Aname，a.partNumber作为ATable来自TableB b，TableA a 其中a.partNumber！= b.partNumber;
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;
查询3:
选择b.id作为Bid，b.name作为Bname，b.partNumber作为BPart，a.id作为 Aid，来自TableB的a.name为Aname，a.partNumber为APart b左连接 a.partNumber上的TableA a不在(从TableB b连接中选择a.id作为Aid) Tablea a on a.partNumber = b.partNumber);
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 TableA a on a.partNumber not in (select a.id as Aid from TableB b join TableA a on a.partNumber=b.partNumber);
有人可以帮助我在这里出错的地方吗?获得结果的正确方法是什么?
Can someone please help me where I am making mistake here? what is the correct way to get the results.
我希望输出是这样的:
+------+-------+-------+------+-------+-------+
| 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 |
+------+-------+-------+------+-------+-------+
所以在这里我的意思是结果中我不想要TableA其中partNumber是10, 20的记录，因为值存在于TableB's partNumber中.
So here what I mean is in the result I don't want the records of TableA where partNumber is 10, 20 because the values are present in TableB's partNumber.
推荐答案
您可以使用以下查询:
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
这个想法是使用NOT EXISTS子句从TableA中选择所有必需的记录.然后CROSS JOIN使用Table1从该查询派生的表中获取所有可能的组合.
The idea is to select all required records from TableA using a NOT EXISTS clause. Then CROSS JOIN the derived table from this query with Table1 to get all possible combinations.
这篇关于如何基于非条件联接2个表的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
