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?
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
