August 22, 2005 at 6:54 pm
I have the following query;
SELECT C.ContactID, (SELECT E.EmailAddress
FROM Contact C INNER JOIN ContactEmail CE ON C.ContactID = CE.ContactID
INNER JOIN Email E ON CE.EmailID = E.EmailID
WHERE C.ContactID = @ContactID
AND CE.EmailTypeID = 1) AS 'Primary',
(SELECT E.EmailAddress
FROM Contact C INNER JOIN ContactEmail CE ON C.ContactID = CE.ContactID
INNER JOIN Email E ON CE.EmailID = E.EmailID
WHERE C.ContactID = @ContactID
AND CE.EmailTypeID = 2) AS 'Secondary'
FROM Contact C
WHERE C.ContactID = @ContactID
Problem is, I need to also get the EmailID as well. I can't place the EmailID in the Sub Query as I can only use 1 Column in the SELECT list. Can someone show me how to do this?
August 22, 2005 at 11:02 pm
Try
SELECT C.ContactID,
E1.EmailAddress AS 'Primary', E1.EmailID AS 'PrimaryEmailID',
E2.EmailAddress AS 'Secondary', E2.EmailID AS 'SecondaryEmailID'
FROM Contact C
LEFT OUTER JOIN (ContactEmail CE1 INNER JOIN Email E1 ON CE1.EmailID = E1.EmailID AND CE1.EmailTypeID = 1)
ON C.ContactID = CE1.ContactID
LEFT OUTER JOIN (ContactEmail CE2 INNER JOIN Email E2 ON CE2.EmailID = E2.EmailID AND CE2.EmailTypeID = 2)
ON C.ContactID = CE2.ContactID
WHERE C.ContactID = @ContactID
August 22, 2005 at 11:27 pm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply