Re: surely, there's got to be a better way

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 07/14/04 

Date: Wed, 14 Jul 2004 10:59:37 -0400

Subqueries are not evil. However, this may help you achieve your objective:

select distinct
    a.*
from
    TABLE_A as a
left join
    TABLE_B as b on a.id = b.TABLE_A_id
                         and a.date = b.TABLE_A_date
where
        b.TABLE_A_id is null
and b.TABLE_A_date is null 

-- 
    Tom
-------------------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON  Canada
www.pinnaclepublishing.com/sql
"Brian W" <brianw@gold_death_2_spam_rush.com> wrote in message
news:Oo%23GUJbaEHA.3352@TK2MSFTNGP12.phx.gbl...
Hello SQL Gurus!
Several years ago I was chastised by a DBA for using sub-querys. And he
would redo my query without it.
Well, that was then, and this is now, and I'll be damned if I can
remember/figure out how to do this simple thing without using a sub-query
Basically I have 2 tables; TABLE_A and TABLE_B
TABLE_A
    id INT
    date DATETIME
    name NVARCHAR(x)
TABLE_B
    TABLE_A_id INT
    TABLE_A_date DATETIME
    description NVARCHAR(x)
TABLE_A.id and TABLE_A.date for the primary key in TABLE_A while TABLE_A_id
and TABLE_A_date are foreign keys into TABLE_A
Basically, I want a query that will give me everything from TABLE_A if it is
NOT in TABLE_B what I came up with was:
 select id, date, name from TABLE_A where (id not in (select TABLE_A_id from
TABLE_B))
The other problem is TABLE_A.id is unique now by may not be.
I'd be grateful is someone could help me out here with this simple problem
with your enormous brains ;-)
TIA
Brian W

Relevant Pages