Topic awaiting preservation: sql-ing an antilist from 2 tables (Page 1 of 1)

my.. brain... hurts..

I'm writing this program in ASP, but I can't figure out the SQL I need to do this one task...

I've got a DB with 2 tables in it:
T1 - a list of all the names (and their associated info) each with a unique ID
T2 - a list of program assignments; each row has an ID from T1 (SID) and an associated PID (prgm id)

I also have a value, XID, which could match any PIDs from T2.

What I need is a recordset of all the rows where PID = XID in T2, and (bc I only need the name info) SID ( fromT2) = ID (fromT1).... then I need a recordset of the opposite, the names in T1 that ARENT in the prgm XID.

For the first, I used:
"SELECT T1.LName,T1.FName,T1.MName,T1.ID FROM T1,T2 WHERE T1.ID=T2.SID AND T2.PID=XID"

But now how do I get a RS of all the IDs in T1 that arent equal to XID (where the specific ID isnt in T2 with a PID = to XID)?

Can anyone help me? Or is this not possible...


[This message has been edited by YOUREanHTML (edited 06-19-2002).]

if I understand you correctly, you should be able to get a list of ID's in T1 that where ID <> XID simply with

SELECT * FROM T2 WHERE (SID <> XID)

the record set returned will contain SID - by inference the ID from T1

if you just want the information from T1 where T2.SID <> T2.XID, then this should do you

SELECT T1.* FROM T2 INNER JOIN T1 ON T2.SID = T1.ID WHERE (T2.SID <> T2.XID)

hth
I:.

hmm..

What I need are all Names from T1 except for the ones that are already in the program represented by value XID.
And I know if the name is already in the program from T2, bc the ID from T2 will match one of the IDs from T1.
For any ID in T1 there could be multiple matches of T2's SID, each with a different PID.
If for a given ID from T1 theres a match in T2 where that same ID (ID=SID) also has an PID=XID, then I don't want it.

Non-statement-wise, doing this would require me taking an ID from T1, then checking every row of T2 to make sure theres no match, then looping back and selecting the next ID from T1 and checking all over again until Ive gone through all the IDs in T1.

Basically, I want what seems to me to be impossible for one sql statement.
Hmm.. if not from one, is there any way to do it from 2 maybe? Or a way better way...

In any case, I'm gonna just grab the entire list from T1 and the list of ppl I dont want and make a slowass nested loop checking thing to load these into the list until I find something more practical. I did it before, and I guess I have no choice but to do it again....

Thanx for the responce someoneInverse.. I know now why inner join wasn't working >.<