If Thierry is right (and that what you perceive is loss, is actually those parents that have child records), then you either have to write a stored procedure or use CTEs (admittedly, I've never used CTEs like this before):

WITH MyCTE(iid, MyLevel, n0, n1, n2, n3) as
(SELECT SELECT lvl0.iid, cast(0 as Integer), lvl0.iid, cast(null as Integer), cast(null as Integer), cast(null as Integer)
FROM mItems lvl0
WHERE lvl0.ipid = 0
UNION ALL
SELECT lvlc.iid, lvlp.MyLevel + 1, lvlp.iid,
case
when lvlp.MyLevel = 0 then lvlc.iid
else lvlp.n1
end,
case
when lvlp.MyLevel = 1 then lvlc.iid
else lvlp.n2
end,
case
when lvlp.MyLevel = 2 then lvlc.iid
else lvlp.n3
end
FROM MyCTE lvlp
JOIN mItems lvlc on ON lvlp.iid = lvlc.ipid
WHERE lvlp.MyLevel < 3)

SELECT cte.n0, cte.n1, cte.n2, cte.n3, lvl.cDesc, cte.iid
FROM MyCTE cte
JOIN mItems lvl on cte.iid = lvl.iid

I hope this works, logically I think it should be what you want (except that you have to extend it to ten levels), but it is all too easy to make stupid mistakes when writing on an email client as opposed to a database tool with a prepare button ;o}

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Teträm Corp
Sent: 1. juli 2009 08:16
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Re: Loosing records with children records in a self join

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:
> >...
>
>

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links