Outer Join does not return null rows 2

[oliveto]

I've got an Outer Join query that has been providing me hours of entertainment today!

I'm curious why the following query won't return an null values. (I'm working in Access query designer)

Thanks for your help.

SELECT MBR_FocusGroups.id AS groupID, MBR_FocusGroups.textPublicTitle AS publicTitle, dbo.MBR_FocusGroupsAssoc.numbContact_ID
FROM MBR_FocusGroups LEFT OUTER JOIN MBR_FocusGroupsAssoc ON MBR_FocusGroups.id = MBR_FocusGroupsAssoc.numbGroup_ID
WHERE (MBR_FocusGroupsAssoc.numbContact_ID = @memberID) OR (MBR_FocusGroupsAssoc.numbContact_ID IS NULL)

 

[sunila7]

Change the query like this...

SELECT MBR_FocusGroups.id AS groupID, MBR_FocusGroups.textPublicTitle AS publicTitle, dbo.MBR_FocusGroupsAssoc.numbContact_ID
FROM MBR_FocusGroups
LEFT OUTER JOIN MBR_FocusGroupsAssoc ON MBR_FocusGroups.id = MBR_FocusGroupsAssoc.numbGroup_ID
AND (MBR_FocusGroupsAssoc.numbContact_ID = @memberID) OR (MBR_FocusGroupsAssoc.numbContact_ID IS NULL)



Sunil

 

[oliveto]

Thank you. That was the result set I was looking for. I guess I havn't had much need for an outer join yet. So, in an outer join, you do some additional filtering before the where clause???

Thanks,

Stephen

 

[SQLBill]

Sunil,

What did you change? I can't see any difference between your script and oliveto's script.

(Except for starting LEFT OUTER JOIN on a new line, which doesn't make a difference.)

-SQLBill

 

[sunila7]

When you use a where clause all the records are filtered which dont satisfy the where condition so, all the records are filtered after retrieving the data. So, the conditions should be put after left outer join so that the data is not filtered out.


Sunil

 

[SQLBill]

Sunil,

Never mind, I finally saw the change in the WHERE to an AND.

-SQLBill