March 27, 2003 at 9:19 am
If I run this query I get a single row as I specify a specific value for col2:
select distinct col2, count(*)
from table1 a, table2 b
where a.col1 = b.col1
and a.col2 = @val1
However, I know that I have values in col1 in table1 which do not appear in col1 table2 so I need to exeute an outer join:
select distinct col2, count(*)
from table1 a left outer join table2 b
on a.col1 = b.col1
and a.col2 = @val1
What I would expect is still a single row but a different number from the first query (where it includes rows where there is no corresponding value in table2).
However, what I get is a list of all the different values of col2 with a count - in effect the last line of the query is ignored.
Have I misunderstood the way outer joins work or is there something strange here?
Jeremy
March 27, 2003 at 9:25 am
What you should to use the left outer join is on the "on" part use the pkeys
and the use where b.col1 is null, like this:
select distinct col2, count(*)
from table1 a left outer join table2 b
on a.col1 = b.col1
and a.col2 = b.col2
where b.col1 is null
March 27, 2003 at 9:29 am
Obvious when you know how.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply