| Subject | Re: nested join sql help |
|---|---|
| Author | Svein Erling |
| Post date | 2003-09-08T09:50:52Z |
--- In firebird-support@yahoogroups.com, "Pete Bray" <pete@s...> wrote:
select n.UserID, b.text from Bugs b
left join Notifications n
on b.id=n.BugID 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.
Set
> Hi Helen, Set,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
>
> 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 b.id=n.BugID 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
select n.UserID, b.text from Bugs b
left join Notifications n
on b.id=n.BugID 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.
Set