How to get multiple child rows from different tables into one result set

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Stearns - 01 Feb 2006 00:53 GMT

I have a table t1(id, other stuff) with 4 dependent (unrelated) tables
ta(id, date, other stuff a), tb(id, date, other stuff b), tc(id, date,
other stuff c), td(id, date, other stuff d). Any or all of the dependent
tables may have rows associated with a given id from t1. What I would
like to do is create a result set with at least 1 row for each row in
t1, and with, maximally, the number number of rows the same as the
largest number of rows from ta, tb, tc, or td associated with each id. A
sample would make my requirement clearer:

t1
id other stuff
1 aaaaa
2 bbbbb
3 ccccc
4 ddddd
5 eeeee

ta
id date otherstuffa
1 1/1/06 a1a1a1
1 1/2/06 a2a2a2
1 1/3/06 a3a3a3
3 1/5/06 a4a4a4
5 1/6/06 a5a5a5

tb
id date otherstuffb
2 1/10/06 b1b1b1
2 1/11/06 b2b2b2
4 1/2/05 b3b3b3

tc
id date otherstuffc
1 1/5/06 c1c1c1
2 1/5/06 c2c2c2
5 1/6/06 c3c3c3

td
id date otherstuffd
1 1/1/06 d1d1d1
3 1/1/06 d2d2d2
3 1/2/06 d3d3d3
3 1/3/06 d4d4d4

What I want as a result set is

1 aaaaa 1/1/06 a1a1a1 (null) (null) 1/5/06 c1c1c1 1/1/06 d1d1d1
1 aaaaa 1/2/06 a2a2a2 (null) (null) (null) (null) (null) (null)
1 aaaaa 1/3/06 a3a3a3 (null) (null) (null) (null) (null) (null)
2 bbbbb (null) (null) 1/10/06 b1b1b1 1/5/02 c2c2c2 (null) (null)
2 bbbbb (null) (null) 1/11/06 b2b2b2 (null) (null) (null) (null)
3 ccccc 1/5/06 a4a4a4 (null) (null) (null) (null) 1/1/06 d2d2d2
3 ccccc (null) (null) (null) (null) (null) (null) 1/2/06 d3d3d3
3 ccccc (null) (null) (null) (null) (null) (null) 1/3/06 d4d4d4
4 ddddd (null) (null) 1/2/06 b3b3b3 (null) (null) (null) (null)
5 eeeee (null) (null) (null) (null) 1/6/06 c3c3c3 (null) (null)

If I only had t1 and ta it would be a simple LEFT OUTER JOIN, but 4
consecutive LEFT OUTER JOINs would yield a much larger result set. I
think I need a new syntax LEFT OUTER JOIN tb TO t1 ON t1.id=tb.id, but I
don't know how to achieve this. Parenthesization occurs to me, but I
don't seehow to apply it. BTW the actual case is not so simple:
otherstuffa is not the same type as otherstuffb, etc.

Reply to this Message

Gert van der Kooij - 01 Feb 2006 01:15 GMT

> I have a table t1(id, other stuff) with 4 dependent (unrelated) tables
> ta(id, date, other stuff a), tb(id, date, other stuff b), tc(id, date,
[quoted text clipped - 13 lines]
> don't seehow to apply it. BTW the actual case is not so simple:
> otherstuffa is not the same type as otherstuffb, etc.

select ....
from t1
left outer join on ta
t1.id = ta.id
left outer join on tb
t1.id = tb.id
left outer join on tc
t1.id = tc.id
left outer join on td
t1.id = td.id

should work

Reply to this Message

Bob Stearns - 01 Feb 2006 07:44 GMT

>>I have a table t1(id, other stuff) with 4 dependent (unrelated) tables
>>ta(id, date, other stuff a), tb(id, date, other stuff b), tc(id, date,
[quoted text clipped - 26 lines]
>
> should work

As I suspected that gives the cross product of the rows of ta, tb, tc,
and td with each id from t1. I want to collapse the result set, so that
it contains only the maximum number of rows associated with id in t1
from (ta, tb, tc, or td) with the other columns (where the number od
rows associated with id from t1) are returned as null.

Reply to this Message

Tonkuma - 01 Feb 2006 08:46 GMT

------------------------------ Commands Entered
------------------------------
SELECT SUBSTR(CHAR(t1.id),1,2) AS id
, SUBSTR(otherstuff,1,5) AS stuff
, TRANSLATE('AB/DE/IJ',CHAR(a.date,USA),'ABcDEfGHIJ') AS datea
, SUBSTR(otherstuffa,1,6) AS stuffa
, TRANSLATE('AB/DE/IJ',CHAR(b.date,USA),'ABcDEfGHIJ') AS dateb
, SUBSTR(otherstuffb,1,6) AS stuffb
, TRANSLATE('AB/DE/IJ',CHAR(c.date,USA),'ABcDEfGHIJ') AS datec
, SUBSTR(otherstuffc,1,6) AS stuffc
, TRANSLATE('AB/DE/IJ',CHAR(d.date,USA),'ABcDEfGHIJ') AS dated
, SUBSTR(otherstuffd,1,6) AS stuffd
FROM t1
LEFT OUTER JOIN
(SELECT ta.*
, ROWNUMBER() OVER(PARTITION BY id) rn
FROM ta
) a
FULL OUTER JOIN
(SELECT tb.*
, ROWNUMBER() OVER(PARTITION BY id) rn
FROM tb
) b
ON a.id = b.id
AND a.rn = b.rn
FULL OUTER JOIN
(SELECT tc.*
, ROWNUMBER() OVER(PARTITION BY id) rn
FROM tc
) c
ON COALESCE(a.id, b.id) = c.id
AND COALESCE(a.rn, b.rn) = c.rn
FULL OUTER JOIN
(SELECT td.*
, ROWNUMBER() OVER(PARTITION BY id) rn
FROM td
) d
ON COALESCE(a.id, b.id, c.id) = d.id
AND COALESCE(a.rn, b.rn, c.rn) = d.rn
ON t1.id = COALESCE(a.id, b.id, c.id, d.id)
ORDER BY
t1.id, COALESCE(a.rn, b.rn, c.rn, d.rn);
------------------------------------------------------------------------------

ID STUFF DATEA STUFFA DATEB STUFFB DATEC STUFFC DATED
STUFFD
-- ----- -------- ------ -------- ------ -------- ------ --------
------
1 aaaaa 01/01/06 a1a1a1 - - 01/05/06 c1c1c1 01/01/06
d1d1d1
1 aaaaa 01/02/06 a2a2a2 - - - - - -

1 aaaaa 01/03/06 a3a3a3 - - - - - -

2 bbbbb - - 01/10/06 b1b1b1 01/05/06 c2c2c2 - -

2 bbbbb - - 01/11/06 b2b2b2 - - - -

3 ccccc 01/05/06 a4a4a4 - - - - 01/01/06
d2d2d2
3 ccccc - - - - - - 01/02/06
d3d3d3
3 ccccc - - - - - - 01/03/06
d4d4d4
4 ddddd - - 01/02/06 b3b3b3 - - - -

5 eeeee 01/06/06 a5a5a5 - - 01/06/06 c3c3c3 - -

10 record(s) selected.

Reply to this Message

Bob Stearns - 01 Feb 2006 22:02 GMT

> ------------------------------ Commands Entered
> ------------------------------
[quoted text clipped - 65 lines]
>
> 10 record(s) selected.

Thanks a lot. That is a beautiful solution, even if I ha to study it for
an hour or two to understand how to apply it in my application.

Here is my implementation in the real application. I have several
questions. Why isn't an extra pair of parentheses requires around the
full outer joins to prevent confusion in the join order? Why are the
commented out WHERE clauses not allowed? The diagnostic says t1.bhid is
not allowed at that point. This runs for over 6 minutes due to the 7!
table scans in the explain plan. cow_bhid is the first part of the
primary key of taa, tba and tca. (bhid, herd_owner) is the whole primary
key of animals_priv.

--------------------------------------------------------------------
select t1.animal_id, t1.locname, t1.bhid,
ta.herd_id, ta.datex, ta.time_code,
ta.datex+283 days as date_due,
tb.herd_id, tb.datex, tb.time_code,
tb.datex+283 days as date_due,
tc.herd_id, tc.in_date, tc.out_end_date,
tc.in_date+282 days as start_date_due,
tc.out_end_date+283 days as end_date_due
from is3.animals2 t1
left outer join
(select taa.*, tab.herd_id,
ROWNUMBER() OVER(PARTITION BY taa.cow_bhid) rn
from is3.service_ai taa
left outer
join is3.animals_priv tab
on tab.herd_owner_id=1
and tab.bhid=taa.bull_bhid
// where taa.cow_bhid=t1.bhid
order by taa.datex) ta
full outer join
(select tba.*, tbb.herd_id,
ROWNUMBER() OVER(PARTITION BY tba.cow_bhid) rn
from is3.service_ai tba
left outer
join is3.animals_priv tbb
on tbb.herd_owner_id=1
and tbb.bhid=tba.bull_bhid
// where tba.cow_bhid=t1.bhid
order by tba.datex) tb
on tb.cow_bhid=ta.cow_bhid
and tb.rn=ta.rn
full outer join
(select tca.*,tcb.herd_id,
ROWNUMBER() OVER(PARTITION BY tca.cow_bhid) rn
from is3.service_pasture tca
left outer
join is3.animals_priv tcb
on tcb.herd_owner_id=1
and tcb.bhid=tca.bull_bhid
// where tca.cow.bhid=t1.bhid
order by tca.in_date) tc
on tc.cow_bhid=coalesce(ta.cow_bhid, tb.cow_bhid)
and tc.rn=coalesce(ta.rn,tb.rn)
on t1.bhid=coalesce(ta.cow_bhid, tb.cow_bhid, tc.cow_bhid)
where t1.bhid in
(select bhid
from is3.animal_sets
where set_name='Bred_Cows'
and userid='jhough')
order by t1.animal_id

Reply to this Message

Art S. Kagel - 01 Feb 2006 15:06 GMT

> I have a table t1(id, other stuff) with 4 dependent (unrelated) tables
> ta(id, date, other stuff a), tb(id, date, other stuff b), tc(id, date,
[quoted text clipped - 4 lines]
> largest number of rows from ta, tb, tc, or td associated with each id. A
> sample would make my requirement clearer:

<SNIP>

Try this:

select *
from ((((t1 left outer join t1a on t1.id=ta.id)
left outer join tb on t1.id=tb.id)
left outer join tc on t1.id=tc.id)
left outer join td on t1.id=td.id)
order by 1,2,3;

Art S. Kagel

Reply to this Message