Tables: First id parent_id - null foreign_id - not null Foreign: id name

When i do select like Query.All<first> it will make SQL like

select * from first p inner join foreign f on (f.id = p.foreign_id) left join first pp on (p.parent_id = pp.id) inner join foreign ff (ff.id = pp.foreign_id)<<--- This is a mistake, this query will hide entries with parent_id = null. It should be left joined or better grouped with parethesis like:

select * from first p inner join foreign f on (f.id = p.foreign_id) left join (select * first pp on (p.parent_id = pp.id) inner join foreign ff on (ff.id = pp.foreign_id)) as bla

Updated at 26.07.2010 6:26:59

here is the test

This thread was imported from our support forum. The original discussion may contain more detailed answer.