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!
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment