Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132

    Post Unanswered: Excluding records from another table

    2 tables
    Code:
    t1
    +----+-------+
    | id | name  |
    +----+-------+
    |  1 | name1 |
    |  2 | name2 |
    |  3 | name3 |
    +----+-------+
    
    t2
    +----+
    | id |
    +----+
    |  2 |
    |  3 |
    +----+
    I'd like to get the name of the ids not in t2.
    I'd like to have this result:
    Code:
    result
    +-------+
    | name  |
    +-------+
    | name1 |
    +-------+
    Thinking about doing something like that
    Code:
    SELECT
     t1.name AS "name"
    FROM
     t1 JOIN t2 ON (t1.id <> t2.id)
    But obvously, this can't work.
    Any idea ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use a LEFT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    You mean doing

    Code:
    SELECT
     t1.name AS "name"
    FROM
     t1 LEFT JOIN t2 ON (t1.id = t2.id AND t2.id IS NULL)
    ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not quite like that, no -- but that was a good try
    Code:
    SELECT
     t1.name 
    FROM
     t1 LEFT JOIN t2 
    ON t1.id = t2.id 
    where t2.id IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Thanks.
    It works

Posting Permissions

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