Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: SELECT id from table A where matching id is not in Table B

    Hi

    Trying to figure out a fairly simple SQL query.

    Basically i have two tables which have both a matching ID column. How ever, i need to find out which ID's are NOT present in BOTH the tables.

    for example

    table a -
    id
    12
    13
    14
    15

    table b -
    id
    12
    14
    15

    Table 'a' has an id of 13 which is not in present in table 'b' and this is the value i need to select!

    thanks in advance

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    select id from a
    left join b on b.id=a.id
    where b.id is null

    or,

    select id from a where id not in (select id from b)

    I find the query plan for the join method (the first one) is often better if the columns are indexed.

  3. #3
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    thanks, great answer...i was trying somthing similiar but using <> instead of 'is null' - its been one of them weeks

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah, he tackles all of the tough ones....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    a simple question needs a simple answer!

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ummm...if he is trying to find all the IDs that are not present in both tables (not just IDs in A that are missing from B), then he should use a full outer join:
    Code:
    select	coalesce(tableA.id, tableB.id)
    from	tableA
    	full outer join tableB on tableA.id = tableB.id
    where	tableA.id is null
    	or tableB.id is null
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    Hi

    This is the actual SQL which is working fine and gets the results i want:

    SELECT V.venue_id FROM VENUE AS V
    LEFT JOIN SUBSCRIPTION AS S ON V.venue_id = S.venue_id
    LEFT JOIN FEATURED AS F ON V.venue_id = F.venue_id
    WHERE S.sub_id = 5 AND F.venue_id IS NULL

    Next problem, how would i do a multiple update, so all the V.venue_id which are returned are used to do an update? Do i need to do a loop or is there a simpler method as i know this doesn't work but gets the idea across:

    UPDATE SUBSCRIPTION SET sub_id = 1 WHERE venue_id = (
    SELECT V.venue_id FROM VENUE AS V
    LEFT JOIN SUBSCRIPTION AS S ON V.venue_id = S.venue_id
    LEFT JOIN FEATURED AS F ON V.venue_id = F.venue_id
    WHERE S.sub_id = 5 AND F.venue_id IS NULL)

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by Brett Kaiser
    Yeah, he tackles all of the tough ones....
    is this meant to be sarcastic? I answer the questions I think I know the answer to... sheesh.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    note that this --

    SELECT V.venue_id FROM VENUE AS V
    LEFT JOIN SUBSCRIPTION AS S ON V.venue_id = S.venue_id
    LEFT JOIN FEATURED AS F ON V.venue_id = F.venue_id
    WHERE S.sub_id = 5 AND F.venue_id IS NULL

    is exactly equivalent to this --

    SELECT V.venue_id FROM VENUE AS V
    INNER JOIN SUBSCRIPTION AS S ON V.venue_id = S.venue_id
    AND S.sub_id = 5
    LEFT JOIN FEATURED AS F ON V.venue_id = F.venue_id
    WHERE F.venue_id IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by jezemine
    is this meant to be sarcastic? I answer the questions I think I know the answer to... sheesh.
    Listen buddy. I'M the tactless one on this forum. (OK, I share the distinction with Rudy....)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what was so tactless about me pointing out the LEFT OUTER JOIN issue?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    ...right, seeming i've got everyones attention, can somone point me in the right direction of multiple updates/deletes.

    For example, if the following SELECT statement returns 100 results:

    SELECT column_a FROM table1 WHERE column_b = 1

    Whats the best way of deleting all records at once from a table which match the results from 'column_a'?

    DELETE FROM table2 WHERE column a = (SELECT column_a FROM table1 WHERE column_b = 1)

    This obviously returns an error because it returns more than one result, so how do i get round this problem? All i need is a keyword or somthing! Looping can't be the only way to do it?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    DELETE FROM table2 WHERE column_a IN (SELECT column_a FROM table1 WHERE column_b = 1)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    Quote Originally Posted by r937
    DELETE FROM table2 WHERE column_a IN (SELECT column_a FROM table1 WHERE column_b = 1)

    Thanks, that answer was also NOT 'tactless'

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That is only because you could not see the smirk on his face as he was typing it. He can't fool the rest of us.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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