CODEWITHSUNDEEP
mysqlsql
bruine
bruine

Reputation: 647

Compare two columns to get data that doesn't same

I wanna compare 2 tables to get data that doesn't same. 

tb1              tb2
==============   ==============
|id| doc_name|   |id| doc_summ|
==============   ==============
|1 | 01180543|   |1 | 01180543|
|2 | Chord   |   ============== 
==============

I wanna compare doc_name and doc_summ. from that example the result must be Chord.

$q = mysql_query(" SELECT t1.doc_name FROM tb1 as t1, tb2 as t2 WHERE t1.doc_name != t2.doc_summ");
while ($row = mysql_fetch_array($q)){
    $doc_copy = $row['doc_name'];
}

but the result still returns all of data. what's wrong? thank you :)

Upvotes: 3

Views: 469

Answers (3)

John Woo
John Woo

Reputation: 263683

You can join both tables using LEFT JOIN. What it does is it only display the records of table 1 if it has no match on table 2.

SELECT  a.*
FROM    tb1 a
        LEFT JOIN tb2 b
            ON a.doc_name = b.doc_summ
WHERE   b.doc_summ IS NULL

Upvotes: 6

Ankur
Ankur

Reputation: 12774

Try this:

SELECT t1.doc_name 
FROM   t1 
WHERE  NOT EXISTS(SELECT t2.doc_summ 
                  FROM   t2 
                  WHERE  t2.doc_summ = t1.doc_name)

The mistake in your query is that you are joining two table, so you will always find a row in t2 which would not satisfy the where condition therefore displaying all data.

DEMO

Upvotes: 4

Ben P.P. Tung
Ben P.P. Tung

Reputation: 1284

Maybe you can try this

select tb1.doc_name from tb1 left join tb2 on tb1.id = tb2.id where tb1.doc_name != tb2.doc_summ;

Upvotes: 0

Related Questions