php 从MYSQL的两个表中选择不匹配的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8265760/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select unmatched records from two tables of MYSQL
提问by user1065055
I have two tables Table1 and Table2 with some records
我有两个表 Table1 和 Table2 有一些记录
id is the common column in both tables and primarykey is set to this column in table1
id 是两个表中的公共列，primarykey 设置为 table1 中的此列
There are many records in table1 and some of these records (not all) are updated into table2.
table1 中有很多记录，其中一些记录（不是全部）更新到 table2 中。
Now I want retrieve from table1 the records not updated into the table2.
现在我想从 table1 中检索未更新到 table2 中的记录。
For example in table1 there are records 1,2,3,4,5,6,7,8,9
例如在 table1 中有记录 1,2,3,4,5,6,7,8,9
And in table2 there are 3,4,7,9
在表2中有3,4,7,9
Now How can I retrieve these records form table1 1,2,5,6 those not updated into table2
现在我如何从 table1 1,2,5,6 中检索这些未更新到 table2 中的记录
I wrote this query :
我写了这个查询：
SELECT Table1.id, Table1.DATE, Table1.C_NAME, Table1.B_NAME
FROM [Table1] INNER JOIN Table2 ON Table1.SLIPNO <>Table2.id;
But the expected result not coming. This query lists all the records repeating each one record manytimes
但预期的结果并没有到来。此查询列出了每条记录重复多次的所有记录
Can any body give me solution to get the expected result.
任何机构都可以给我解决方案以获得预期的结果。
回答by Zohaib
select *
from table1
where table1.slip_no NOT IN (select id from table2)
Assuming name of common column is id
假设公共列的名称是 id
Or you can modify your query as
或者您可以将查询修改为
SELECT distinct (Table1.id, Table1.DATE, Table1.C_NAME, Table1.B_NAME)
FROM [Table1]
INNER JOIN Table2 ON Table1.SLIPNO <>Table2.id
回答by Tristian
回答by Raymond Hettinger
回答by Vaibhav Jain
Try this
尝试这个
SELECT Table1.id, Table1.DATE, Table1.C_NAME, Table1.B_NAME FROM [Table1]
WHERE
NOT EXISTS (SELECT * from Table2 WHERE Table1.SLIPNO !=Table2.id );
回答by Sivaguru
You can use the following query
您可以使用以下查询
SELECT id FROM database1.table WHERE id NOT IN(SELECT id FROM database2.table)
回答by user8749645
inner joinwill not help. To get unmatched records I tried this:
inner join不会有帮助。为了获得无与伦比的记录，我试过这个：
SELECT
A.ID,A.DATE,A.NAME
FROM TABLE1 A
WHERE CONCAT(A.ID , A.DATE ,A.NAME)
NOT IN
(SELECT CONCAT(B.ID , B.DATE ,B.NAME) as X
from TABLE2 B) ;
