March 24, 2004 at 12:26 pm
Is there a way to do the following without using a UDF?
Goal: To return all client records with their primary email if they have one, NULL if not.
All client records can have to 0 to many email records, only one of which will have the IsPrimaryEmail bit set.
Returning columns from two tables Clients and Email
Pseudo code:
SELECT C.Col1, C.Col2, E.Col1 FROM Clients C
LEFT OUTER JOIN Emails E ON E.ClientID=C.ClientID
WHERE E.IsPrimaryEmail=1
---------------------------------------
E.IsPrimaryEmail is a bit col
The above will of course return only those that have a primary email but skip those that do not have a primary. I need it to return all clients that match the client selection criteria with either the primary email (if one exists) or NULL.
I am currently doing this with a UDF, which works fine, but I need to apply this same principle across multiple tables/joins.
March 24, 2004 at 12:39 pm
Your psuedo code above is slightly flawed. By putting the restriction in a where clause rather than the join you are in effect turning the query into an inner join. IE:
SELECT C.Col1, C.Col2, E.Col1
FROM Clients C
LEFT OUTER JOIN Emails E ON E.ClientID=C.ClientID
AND E.IsPrimaryEmail=1
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
March 24, 2004 at 12:43 pm
Ah...of course!
Thanks for pointing this out...I've been staring at this code for longer than I want to admit.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply