Post reply

getting all records not in a table

  • December 14, 2004 at 1:25 pm

    #60574

    OK, here's my problem: I have an orders table, oeOrders, which has a primary key based on a billing ID, [_billId], and a revision #, [_rev]. I need to pull all records that do not have a related record in another table, [oeCreditCards], which has the same [_billId] and [_rev] fields. What would be the easiest way to do this? I seem to be stumped.

    Thanks

  • December 14, 2004 at 1:34 pm

    #533841

    Do you have a field in the oeCreditCards table besides _billId and _Rev that does not allow nulls?

    I generally will create a left join from oeOrders to oeCreditCards on your two fields and include a where clause of b.fieldname is null. That will give you all the records in the left table that do not have a cooresponding record in the right table.



    Michelle

  • December 14, 2004 at 1:55 pm

    #533845

    Have a look at EXISTS in BOL. If I understand you right, this should do what you want.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 14, 2004 at 2:06 pm

    #533847

    Thanks to both of you, I tried both responses, and each works. I'll have to check the execution plan to see which is more efficient. Thanks again.

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

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