|
|
Posted by Erland Sommarskog on 07/09/05 00:52
jqq (jqq@myrealbox.com) writes:
> My apologies for leaving out the data, I didn't want to get too long in
> my original post if it wasn't needed. Please see below (including one
> fix on a table).
>
> So, the current query will pull John Smith's "standard" addresses on
> Main, Second, and Third streets, plus Frank Doe's "standard" addresses
> on Main, Second and Third streets.
>
> The results I need would give John Smith's "standard" addresses on
> Main, Second, and Third streets, plus Frank Doe's "alternate" addresses
> on Fifth and Sixth streets.
This query gives the result described above, but does not match your
description in the first post. But maybe you simply messed up on all
these terrible 10-letter codes when you composed the sample data.
SELECT C.CONTACT_X, C.LONGNAME, CA.ADDRESSTYPE_REFX,
R.DESCRIPTION AS Type_DESCRIPTION, CA.ADDRESS_X,
A.ADDRESSLINE1, A.ADDRESSLINE2, A.CITY, A.STATE, A.ZIPCODE,
A.PHONE
FROM CONTACTS C
JOIN CONTACTADDRESSES CA ON C.CONTACT_X = CA.CONTACT_X
JOIN ADDRESSES A ON CA.ADDRESS_X = A.ADDRESS_X
JOIN REFERENCETABLE R ON CA.ADDRESSTYPE_REFX = R.REFERENCETABLE_X
WHERE C.ACTIVE = 1
AND CA.ADDRESSTYPE_REFX IN ('MN30D843J2', 'SC93JDL39D')
AND CA.ACTIVE = 1
AND C.CONTACT_X IN (SELECT M.CONTACT_X
FROM MASTERTABLE M
WHERE M.ACTIVE = 1
AND M.RECORDTYPE = 'E')
AND NOT EXISTS (SELECT *
FROM CONTACTADDRESSES CA1
WHERE C.CONTACT_X = CA1.CONTACT_X
AND CA1.ADDRESSTYPE_REFX IN ('ASKD943KDI'))
UNION ALL
SELECT C.CONTACT_X, C.LONGNAME, CA.ADDRESSTYPE_REFX,
R.DESCRIPTION AS Type_DESCRIPTION, CA.ADDRESS_X,
A.ADDRESSLINE1, A.ADDRESSLINE2, A.CITY, A.STATE, A.ZIPCODE,
A.PHONE
FROM CONTACTS C
JOIN CONTACTADDRESSES CA ON C.CONTACT_X = CA.CONTACT_X
JOIN ADDRESSES A ON CA.ADDRESS_X = A.ADDRESS_X
JOIN REFERENCETABLE R ON CA.ADDRESSTYPE_REFX = R.REFERENCETABLE_X
WHERE C.ACTIVE = 1
AND CA.ADDRESSTYPE_REFX IN ('AM39DK3KD9', 'ASKD943KDI')
AND CA.ACTIVE = 1
AND C.CONTACT_X IN (SELECT M.CONTACT_X
FROM MASTERTABLE M
WHERE M.ACTIVE = 1
AND M.RECORDTYPE = 'E')
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
[Back to original message]
|