Quering multiple tables

[abheja]

I have two TABLES [A] and with these attributes.
A
-ssn
-id1

B
-ssn
id2

I want to find all SSN from A whose id1 does not exist in id2. I can find all ssn in A not in SSN B.
Select * from A where ssn not in(select ssn from A, B where a.ssn=b.ssn)
but how do I get all the SSN and the ID1 from A not in B?
Thanks.

 

[PHV]

One way:
SELECT *
FROM A
WHERE id1 NOT IN (SELECT id2 FROM B)

Another way:
SELECT A.*
FROM A LEFT JOIN B ON A.id1 = B.id2
WHERE B.id2 IS NULL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[abheja]

Thanks PHV, however what I am trying to find is the following.
A
-ssn
-orderid

B
-ssn
orderid

I want the Customers(SSN) in A whose order(orderid) is not in B. SSN is unique but order id is not so customers could have the same orderid.
Thanks

 

[PHV]

Like this ?
SELECT *
FROM A
WHERE NOT EXISTS(SELECT * FROM B WHERE ssn=A.ssn AND orderid=A.orderid)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[abheja]

Ha!
This works!! What I've been doing is the following:

SELECT *
FROM A
WHERE NOT EXISTS(SELECT * FROM A,B WHERE B.ssn=A.ssn AND B.orderid=A.orderid)

Sorry, I am new to SQL so trying to understand why this did not work.
Thanks so much, PHV!!!