Results 1 to 3 of 3

Thread: Anti-joins

  1. #1
    Join Date
    Jun 2007
    Posts
    40

    Unanswered: Anti-joins

    Sorry to bother you again but do you know how to create a join between two
    tables and only pull off records which don't satisfy the join condition?

    To illustrate, I created a test database which looks like the following:

    Table1:
    Code:
    Key1 Name1
       1  Adam
    Table2:
    Code:
    Key1 Key3
       1    1
       1    3
    Table3:
    Code:
    Key3 Name3
       1   one
       2   two
       3 three
       4  four
    Table2 is to be viewed as a link table between Table1 and Table3. So if I
    want to pull off all records which satisfy this link, I run

    Code:
    SELECT Table1.Name1, Table3.Name3
    FROM (Table1 INNER JOIN Table2 ON Table1.Key1=Table2.Key1) INNER JOIN
    Table3 ON (Table2.Key3=Table3.Key3);
    This produces as expected:

    Code:
    Name1 Name3
     Adam   one
     Adam three
    Now if I want to make the inverse query, namely one which looks like:

    Code:
    Name1 Name3
     Adam   two
     Adam  four
    So I tried to do this query:

    Code:
    SELECT Table1.Name1, Table3.Name3
    FROM (Table1 INNER JOIN Table2 ON Table1.Key1=Table2.Key1) INNER JOIN
    Table3 ON NOT (Table2.Key3=Table3.Key3);
    But I got a weird result:

    Code:
    Name1 Name3
     Adam   two
     Adam three
     Adam  four
     Adam   one
     Adam   two
     Adam  four
    So if I want to produce a query which looks like

    Code:
    Name1 Name3
     Adam   two
     Adam  four
    Then what SQL code should I use?

    Any help would be much appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Table1.Name1
         , Table3.Name3
      FROM (
           Table1 
    CROSS
      JOIN Table3
           )
    LEFT OUTER
      JOIN Table2
        ON (
           Table2.Key1 = Table1.Key1
       AND Table2.Key3 = Table3.Key3
           )
     WHERE Table2.Key1 IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by kidburla
    But I got a weird result:
    That result isn't weird; it's exactly what I would expect from that SQL statement.

Posting Permissions

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