April 25, 2008 at 7:36 am
I'm sure i'm missing something obvious:
i'm working on a database where a contacts table with postaladdressID, emailID etc refer to the ID columns in PostalAddress and Email tables. Sometimes the value of an emailID or postaladdressID = -1 which indicates "use the preferred address" etc.
tContacts:
ID int, personID int, emailID int, postaladdressID int
tEmail:
emailID int, personID int, emailaddress varchar(50), prefer bit
tPostalAddress:
postaladdressID int, personID int, address varchar(500), prefer bit
how might i go about returning results where if tContacts.emailID = -1 or tContacts.postaladdressID = -1, the returned results provide the actual preferred addressID / emailID (flagged by tEmail.prefer or tPostalAddress.prefer) rather than -1.
there is only 1000 rows in tContacts so speed not so important but it would still be interesting to know the most efficient way.
Thanks for any help.
April 25, 2008 at 8:18 am
Give this a try:
SELECT PersonId,
CASE WHEN c.EmailId = -1 THEN e.EmailAddress ELSE NULL END AS PreferredEmail,
CASE WHEN c.PostalAddressId = -1 THEN p.Address ELSE NULL END AS PreferredAddress
FROM tContacts c
LEFT JOIN tEmail e ON c.PersonId = e.PersonId AND e.Prefer = 1
LEFT JOIN tPostalAddress p ON c.PersonId = p.PersonId AND p.Prefer = 1
WHERE EmailId = -1
OR PostalAddressId = -1
Dave Novak
April 25, 2008 at 9:01 am
thanks very much. wish i had asked earlier and saved myself an hour trying to work it out using table variable etc. I'll give this a go and let you know.
April 28, 2008 at 5:00 am
i changed your code a little to return the actual emailid etc where it was available rather than null. Works very well. Thanks for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply