Hi,

I guess you're misunderstanding LEFT JOIN

it doesn't mean "add all records from first table and join them as much
as possible to records of second one... and then add a dummy record from
first table even if it can be joined to a record a second table"
but only "add all records from first table and join them as much as
possible to records of second one"

in the first join request you showed, you'll see "Inventory, 8" record
only if there's no record in mItems which have level 0 as parent
remove all colaesce and shows every fields and I guess you will
understand where your mistake is
you can't have a record for parent level AND a record for each children
level
with a join, you'll only have children levels

if i'm not wrong, what you need could be a stored proc

the problem in second join request looks the same
it's just amplified by a second left join

Thierry

Bhavbhuti Nathwani a écrit :

>
>
> Hi Svein
>
> I am sorry for not being able to explain the problem better. I will
> try again with actual examples:
>
> SELECT lvl0.iid AS n0,
> lvl0.cDesc AS cDesc,
> lvl0.iID AS iID
> FROM mItems lvl0
> WHERE lvl0.ipid = 0
> returns one record: 8, Inventory, 8
> this is correct as it is the top most in the selfjoin
>
> SELECT lvl0.iid AS n0,
> lvl1.iid AS n1,
> COALESCE(lvl1.cDesc, lvl0.cDesc) AS cDesc,
> COALESCE(lvl1.iID, lvl0.iID) AS iID
> FROM mItems lvl0
> LEFT JOIN mItems lvl1 ON lvl1.ipid = lvl0.iid
> WHERE lvl0.ipid = 0
> returns 7 records instead of 8 (the above Inventory, 8 record is missing)
> 8, 9, Raw Material, 9
> 8, 10, Semi-Finished, 10
> ...
> 8, 15, Trading Goods, 15
>
> SELECT lvl0.iid AS n0,
> lvl1.iid AS n1,
> lvl2.iid AS n2,
> COALESCE(lvl2.cDesc, lvl1.cDesc, lvl0.cDesc) AS cDesc,
> COALESCE(lvl2.iID, lvl1.iID, lvl0.iID) AS iID
> FROM mItems lvl0
> LEFT JOIN mItems lvl1 ON lvl1.ipid = lvl0.iid
> LEFT JOIN mItems lvl2 ON lvl2.ipid = lvl1.iid
> WHERE lvl0.ipid = 0
> returns 9 records, record from 1st and many from 2nd SQL are missing
> 8, 9, 54, rm1, 54 -- child of Raw Material, 9
> 8, 9, 55, rm2, 55 -- child of Raw Material, 9
> 8, 10, null, Semi-Finished, 10 -- as it does not have child-records
> ...
> The record for Raw Material, 9 is missing because it's children appear
> in the recordset, same as missing Inventory, 8 in second SQL
> But as Semi-Finished does not have any child records, it appears in
> the recordset.
>
> Hope this explains what I have noticed.
>
> Thanks for your response.
> Kind regards
> Bhavbhuti
>
> --- In firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.com>, Svein Erling Tysvaer
> <svein.erling.tysvaer@...> wrote:
> >...
>
>