Page 1 of 1

Data Filtering

Posted: Thu Jun 07, 2007 10:08 pm
by pcoder
Hi,
In one table i have a data like this
Table1

code1 code2 code3
12 1 1
12 1 2
12 2 1

And in an another table i have a data like this

Table2
code1 code2 code3
12 1 1

Code1 is common in both table
But i need only those data which are not in Table2.
ie.

code1 code2 code3
12 1 2
12 2 1

How can write the query to get those data.

Thanks

Posted: Thu Jun 07, 2007 11:05 pm
by anjanesh

Code: Select all

SELECT `code1`,`code2`,`code3` FROM `Table1`
WHERE `code1` NOT IN (SELECT `code1` FROM `Table2`)

Posted: Fri Jun 08, 2007 1:45 am
by pcoder
Thanks for your quick response.
But here code1, code2 and code3 is a different field in database.
where code1=12 and code2=1 and code3=1

thanks

Posted: Thu Jun 14, 2007 6:53 pm
by califdon
anjanesh gave you the answer.

Posted: Fri Jun 15, 2007 3:02 am
by Gente
anjanesh wrote:

Code: Select all

SELECT `code1`,`code2`,`code3` FROM `Table1`
WHERE `code1` NOT IN (SELECT `code1` FROM `Table2`)
Seems this code in this situation is equal to

Code: Select all

SELECT `code1`,`code2`,`code3` FROM `Table1`
WHERE `code1` NOT IN (12)
So it returns empty result.

Can you try it

Code: Select all

SELECT `Table1`.`code1`, `Table1`.`code2`,`Table1`.`code3` 
FROM `Table1`
INNER JOIN `Table2`
ON (`Table1`.`code1` != `Table2`.`code1` OR `Table1`.`code2` != `Table2`.`code2` OR `Table1`.`code3` != `Table2`.`code3`)
Also this solution works even on MySQL 3.23