Data Filtering

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
pcoder
Forum Contributor
Posts: 230
Joined: Fri Nov 03, 2006 5:19 am

Data Filtering

Post 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
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Code: Select all

SELECT `code1`,`code2`,`code3` FROM `Table1`
WHERE `code1` NOT IN (SELECT `code1` FROM `Table2`)
User avatar
pcoder
Forum Contributor
Posts: 230
Joined: Fri Nov 03, 2006 5:19 am

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

anjanesh gave you the answer.
User avatar
Gente
Forum Contributor
Posts: 252
Joined: Wed Jun 13, 2007 9:43 am
Location: Ukraine, Kharkov
Contact:

Post 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
Post Reply