Post reply

The clause in a simple select statement

  • January 13, 2006 at 10:05 am

    #93167

    hello

    Having problems with a select that I'm hoping someone can help me with....

    All I want to do is to get all the records from table A that do not have a matching id column in table b.

    I tried this:

    SELECT *

    FROM TableA, TableB

    WHERE TableA.IDColumn <> TableB.IDColumn

    But this matches the first column in Table A loads of times against every column of table B.

    I don't know what I'm doing wrong.

    I tried slipping in a distinct statement but still no success.

    It seems so simple but I can't work it out.

    ta

    Ben

  • January 13, 2006 at 10:18 am

    #614689

    Here's a couple ways:

    select * from tableA join TableB on TableA.IDColumn <> TableB.IDColumn

    Select * from tableA where TableA.IDColumn not in (Select IDColumn from TableB)

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • January 13, 2006 at 10:28 am

    #614691

    Thanks mate,

    The second one works a treat

    Thanks

  • January 15, 2006 at 11:27 pm

    #614836

    This one will also work.

    SELECT * FROM TableA LEFT JOIN TableB ON TableA.ID=TableB.ID

    WHERE TableB.ID IS NULL.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply