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

MvdM · #1 (**permalink**) 11-02-04, 02:51

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..

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 ..

r937 · #2 (**permalink**) 11-02-04, 02:59

well, that should work, the only thing i can suggests is don't use USING

Code:

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

MvdM · #3 (**permalink**) 11-02-04, 03:03

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..

r937 · #4 (**permalink**) 11-02-04, 03:07

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?

MvdM · #5 (**permalink**) 11-02-04, 03:13

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