Friday, March 9, 2012

JOIN to get rows in one table that are not in the other

I've got two identical tables: sw_scan and software.
Now I want to get all rows from sw-scan that don't exist in table software.

With my statement I get all of the rows from table 1 with NULL for all table 2 values.. :confused:

SELECT *
FROM sw_scan
LEFT JOIN software USING(Manufacturer,Product,Version,Computer)
WHERE software.manufacturer IS NULL;

oh, and (Manufacturer, Product, Version, Computer) is primary key in both tables ..well, that should work, the only thing i can suggests is don't use USING
SELECT *
FROM sw_scan
LEFT
JOIN software
on sw_scan.Manufacturer
= software.Manufacturer
and sw_scan.Product
= software.Product
and sw_scan.Version
= software.Version
and sw_scan.Computer
= software.Computer
WHERE software.Manufacturer IS NULL|||hmm.. thanks..
but I already tried lots of things.. to be sure.. and this was one of them...
sth really weird is going on here.. :(|||well, the table 2 columns should all be null, because that's what you're looking for, right?

by the way, what database is this?|||They should be NULL... but the resultset should only contain the rows that have NULL at the software.Manufacturer field after the JOIN.
I used exactly the smae statements to create and fill the tables, only changing the table name, and afterwards I deleted 10 rows from software...
So my resultset should have ten rows. Instead it outputs all 1000 rows filling all fields from table software with NULL.

It's a MySQL 4 db|||do you get all 990 rows when you change LEFT to INNER?|||When I make it an INNER JOIN, I get an empty set..
And if I also get rid of the WHERE clause I get the 990 rows...|||i'm running out of ideas

try REPAIRing the database|||Solved !

After I dropped the tables.. recreated them (with exactly the same statement) and filled them again, it worked..

thnx for the help Rudy!

No comments:

Post a Comment