Subject Re: nested join sql help
Author Svein Erling
--- In, "Pete Bray" <pete@s...> wrote:
> Hi Helen, Set,
> i found a solution once i described the problem further in the
> earlier replies to you.
> select n.UserID, b.text from Bugs b left outer join Notifications n
> on where (n.UserID='T') or (n.UserID is null)
> does this look the most sensible way to go? it certainly produces the
> data i want

Nope, Pete, I do not like your solution. I will repeat myself: "I have read (no practical experience myself) that the only way to limit rows from the right side of a left join is in the join clause". This means that your statement should look like

select n.UserID, b.text from Bugs b
left join Notifications n
on and n.UserID = 'T'
where <criteria limiting the Bugs table and tables joined to it by inner joins>

Since you are doing a left join, it is not sensible to put any criteria belonging to Notifications in the where clause, it should go in the join clause. Although I did notice that Helen did put n.userid in the where clause, and that confuses me. She probably knows something that I do not (when we disagree, she is normally correct), and will jump in correcting what I wrote.