Complex SQL query
Posted on 2004-04-28
I have 2 tables:
I want run a select query with where clause that pulls up clients that DO and DO NOT have addresses associated with them in Clients_Addresses table. Here is what I have so far but it only pulls up clients that have addresses and I want to pull up the ones that don't as well:
select distinct (C.ClientID), COALESCE(CLN.ClientLName, '') + ', ' + COALESCE(CLN.ClientFName, '') + ' '
+ COALESCE(CLN.ClientMName, '') As ClientName, C.DOB, COALESCE(A.StNum, '')+' '+COALESCE(A.StName, '')
+','+COALESCE(A.UnitNum, '')+','+COALESCE(A.ExtraAddrLine, '')+','+COALESCE(A.City, '')+','
+COALESCE(CT.LongDesc, '')+','+COALESCE(A.Zip, '') As AddressCol
from CLIENT C, CLIENT_NAME CLN, ADDRESS A, ADDRESS_OWNER AO, CODE_TABLE CT, CDSS_CASE CDC
where((C.ClientID = CLN.ClientID)
and (C.ClientID = AO.OwnerID)
and AO.AddrType = 889
And AO.OwnerTypeCD = 880 and AO.AddrID = A.AddrId and A.StateCD *= CT.CodeID)
AND [CLN].[ClientLName] like 'bush'
order by C.ClientID
Thanks a lot