With a minor change I got it to work (joining the details before getting to the master):

with cted1(mrec, tmp1, MyOrder) as
(select d1.MREC, d1.TMP1, (select count(*) from TESTDETAIL1 d1b
where d1.MREC = d1b.MREC and d1.RECID > d1b.RECID)
from TESTDETAIL1 d1),
cted2(mrec, tmp2, MyOrder) as
(select d2.MREC, d2.TMP2, (select count(*) from TESTDETAIL2 d2b
where d2.MREC = d2b.MREC and d2.RECID > d2b.RECID)
from TESTDETAIL2 d2),
cted3(mrec, TMP1, TMP2) as
(select coalesce(cted1.MREC, cted2.MREC), cted1.TMP1, cted2.TMP2
from cted1
full join cted2 on cted1.MREC = cted2.MREC and cted1.myorder = cted2.myorder)

select m.TMP, d.TMP1, d.TMP2
from TESTMASTER m
join cted3 d on m.MREC = d.MREC

HTH,
Set
________________________________________

> Can i select detail tables like this???
>
> M.Tmp, D1.Tmp1, D2.Tmp2
> A D1A1 AAA1
> A D1A2 AAA2
> A <null> AAA3
> B D1B1 BB1
> B D1B2 BB2
> B D1B3 <null>
>
> from the following tables:
>
> TESTMASTER
> MREC TMP
> 1 A
> 2 B
>
> TESTDETAIL1
> RECID MREC TMP1
> 1 1 D1A1
> 2 1 D1A2
> 3 1 D1B1
> 4 1 D1B2
> 5 1 D1B3
>
> TESTDETAIL2
> RECID MREC TMP2
> 1 1 AAA1
> 2 1 AAA2
> 3 1 AAA3
> 4 2 BB1
> 5 2 BB2

This may be simple conceptually, but practically it seems difficult. I tried using two recursive CTEs, but once I added the second CTE I got an error on the first CTE. This might have been due to me not using this PC for anything fancy so that it might work on a newer Firebird version. However, I chose a different approach that I think should work (again, it failed for me), try it and report back to the list:

with cted1(mrec, tmp1, MyOrder) as
(select d1.MREC, d1.TMP1, (select count(*) from TESTDETAIL1 d1b
where d1.MREC = d1b.MREC and d1.RECID > d1b.RECID)
from TESTDETAIL1 d1),
cted2(mrec, tmp2, MyOrder) as
(select d2.MREC, d2.TMP2, (select count(*) from TESTDETAIL2 d2b
where d2.MREC = d2b.MREC and d2.RECID > d2b.RECID)
from TESTDETAIL2 d2)

select coalesce(tm1.tmp, tm2.tmp) as tmp, td1.TMP1, td2.TMP2
from TESTMASTER tm1
join cted1 td1 on tm1.MREC = td1.MREC
full join (cted2 td2
join TESTMASTER tm2 on tm2.MREC = td2.MREC) on td1.MREC = td2.MREC and td1.MyOrder = td2.MyOrder

HTH,
Set



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

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

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



http://docs.yahoo.com/info/terms/