469,306 Members | 2,443 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

How to get multiple child rows from different tables into one resultset

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.
Feb 1 '06 #1
5 1378
In article <iG***************@fe03.lga>, Bob Stearns (rstearns1241
@charter.net) says...
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:

[sample data snipped]

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.

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
Feb 1 '06 #2
Gert van der Kooij wrote:
In article <iG***************@fe03.lga>, Bob Stearns (rstearns1241
@charter.net) says...
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:

[sample data snipped]

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.


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

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.
Feb 1 '06 #3
------------------------------ 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.

Feb 1 '06 #4
Bob Stearns wrote:
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:

<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
Feb 1 '06 #5
Tonkuma wrote:
------------------------------ 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.

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

Feb 1 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by RJ | last post: by
4 posts views Thread by Jeff Thies | last post: by
9 posts views Thread by Ed_No_Spam_Please_Weber | last post: by
reply views Thread by Mike Gorgone | last post: by
8 posts views Thread by Rick | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.