Thread: Left Join 3 tables

Hi Group - 

1) I have 3 tables: A, B and C
2) Each table has an unqiue associative field called cust_id
3) The cust_id HAS to be in table A, the cust_id *could* be in table
B, and the cust_id *could* be in table C
4) I need my query to:  a) count all rows where cust_id is in table A but *not* in table B
AND *not* in table C b) display all emails where cust_id is in table A but *not* in table
B AND *not* in table C

Here's some of my queries of what I have:

This one is close I think:

select distinct A.email from A
left join A as ics on ics.custid = A.custid
left join B on B.custid = A.custid
left join C on C.custid  = A.custid
where ics.custid <> A.custid;

THANK YOU!

dave

On Wednesday 14 May 2003 18:35 in
<27ad91d2.0305140935.4fdec226@posting.google.com>, Hunter
(dave_h4@yahoo.com) wrote:

> 4) I need my query to:
>   a) count all rows where cust_id is in table A but *not* in table B
> AND *not* in table C

SELECT COUNT(*) FROM A  WHERE NOT EXISTS(SELECT 1 FROM B WHERE B.cust_id = A.cust_id)    AND NOT EXISTS(SELECT 1 FROM C
WHEREC.cust_id = A.cust_id);
 

>   b) display all emails where cust_id is in table A but *not* in table
> B AND *not* in table C

SELECT A.email FROM A  WHERE NOT EXISTS(SELECT 1 FROM B WHERE B.cust_id = A.cust_id)    AND NOT EXISTS(SELECT 1 FROM C
WHEREC.cust_id = A.cust_id);
 

-- 
Regards,

Dave
======================================================
dwnoon@spamtrap.ntlworld.com (David W Noon)
Remove spam trap to reply via e-mail.
======================================================