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!
Showing posts with label software. Show all posts
Showing posts with label software. Show all posts
Subscribe to:
Posts (Atom)