Mike Gercevich writes "Dear Guru:
Here is an annoying one that I ran across today. I expected that a simple LEFT JOIN would give the results that I was looking for, turns out, when I use conditional statements on the RIGHT Table, it gives something that looks like a RIGHT JOIN result set. Go figure!
tblNumberType:
PK intNumberTypeID - Integer
txtNumberTypeDesc - Text
tblNumbers:
PK intNumberTypeID - Integer
PK intFirmID - Integer
PK intRoleLookupID - Integer
txtNumber - Text
txtExt - Text
I would like to return a recordset to populate an array. The Recordset should have all of the intNumberTypeID and txtNumberTypeDesc from tblNumberType in the first two columns and will populate the second two colums with txtNumber and txtExtension that match a intFirmID/intRoleLookupID combination. Sounds easy enough. here is sample data...
select * from tblNumberType
intNumberTypeID txtNumberTypeDesc
--------------- ------------------------------
1 Business Phone
2 Business FAX
3 Toll Free Phone
4 Home Phone
select * from tblNumbers
intNumberTypeID intFirmID intRoleLookupID txtNumber txtExt
--------------- --------- --------------- --------- ------
1 9 1 222-1234 3001
3 9 1 800-1234
1 9 2 222-5656 2277
4 9 2 777-2525
1 10 1 888-2626 1111
1 10 2 888-2626 2222
*The following statement yeilds the shown results:
SELECT a.intNumberTypeID,a.txtNumberTypeDesc,b.txtNumber,b.txtExt
FROM tblNumberType as a LEFT JOIN tblNumbers as b ON (
a.intNumberTypeID = b.intNumberTypeID )
WHERE b.intFirmID = 9 AND b.intRoleLookupID = 1
ORDER BY intNumberTypeID
intNumberTypeID txtNumberTypeDesc txtNumber txtExt
--------------- -------------------- ---------- ------
1 Business Phone 222-1234 3001
3 Toll Free Phone 800-1234
*The result I am looking for is as follows...
intNumberTypeID txtNumberTypeDesc txtNumber txtExt
--------------- -------------------- ---------- ------
1 Business Phone 222-1234 3001
2 Business FAX
3 Toll Free Phone 800-1234
4 Home Phone
Thanks in advance,
Mike Gercevich
P.S. I actually did come up with a solution on my own, but it looks really compute intensive and just plain ugly...i'd like to compare my solution to the better solution that i am confident you will conjure up! :)"
Return all rows of LEFT Table in a LEFT JOIN when conditions are on RIGHT Table? 
Posted - 2001-02-05 : 23:28:53