Re: join condition against where with coalesce

I figured out my problem.
Table1 and Table2 have matches for every pk,fk just not on typeid=14,
therefore when I join on just the pk,fk and do a where looking for null, it
doesn't find any rows that qualify.

Doesn't help me solve my problem, but at least I know where I'm at.

Sim

Sim Zacks wrote:
> Should there be any difference between:
>
> select * from table1 a left join table2 b on a.pk=b.fk and b.typeid=14
> and
> select * from table1 a left join table2 b on a.pk=b.fk
> where coalesce(b.typeid,14)=14
>
> The reason I need to use the coalesce is because my goal is to do it
> with a full join and can't use
> the and condition because it is not merge-joinable.
>
> My test with the left join showed me that with the where it doesn't give
> any results, while I would expect it to give me all the results in the
> first table.
>
> Any thoughts?