Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2004
    Posts
    5

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

    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 ..
    Last edited by MvdM; 11-02-04 at 03:53.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Posts
    5
    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..

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Posts
    5
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you get all 990 rows when you change LEFT to INNER?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2004
    Posts
    5
    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...

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm running out of ideas

    try REPAIRing the database
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2004
    Posts
    5

    Thumbs up

    Solved !

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

    thnx for the help Rudy!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •