How to interpret rows from a mutli-table SELECT?

How to interpret rows from a mutli-table SELECT? I want to do a SELECT from a parent and it's children and use the resulting row as a basis for inserting into another table. Suppose the schemas are as follows : table parent parentkey parentdata table child1 parentkey child1key child1data table child2 parentkey child2key child2data If I do select * from parent a, outer child1 b,outer child2 c where a.parentkey = b.parentkey and a.parentkey = c.parentkey order by a.parentkey,b.child1key,b.child1data,b.child2key,b.child2data The number of rows returned seems to be number of parent rows x number of child1 rows x number of child2 rows Is this correct? Why does it do that? How do I interpret one of the rows returned in terms of which child row it came from? Thanks! Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

Your two OUTER statements is what is causing child1*child2 records to be returned. Can you do it as two seperate selects? Another solution is to UNION two SELECT's, one from each table with NULL's/defaults on the other tables fields. S.W. <strangiato@my-deja.com> wrote in message news:7rjq8q$9op$1@nnrp1.deja.com... > How to interpret rows from a mutli-table SELECT? > > I want to do a SELECT from a parent and it's children and use the > resulting row as a basis for inserting into another table. > > Suppose the schemas are as follows : > > table parent > > parentkey > > parentdata > > > > table child1 > > parentkey > > child1key > > child1data > > > > table child2 > > parentkey > > child2key > > child2data > > If I do > > select * from parent a, outer child1 b,outer child2 c > where a.parentkey = b.parentkey > and a.parentkey = c.parentkey > order by a.parentkey,b.child1key,b.child1data,b.child2key,b.child2data> > The number of rows returned seems to be number of parent rows x number > of child1 rows x number of child2 rows > > Is this correct? > > Why does it do that? > > How do I interpret one of the rows returned in terms of which child row > it came from? > > Thanks! > > > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't.

That's a good suggestion. Thanks. Is UNION an efficient construct? In article <204285895F88D1118FAC00A0C933CDDF348804@mail.silverstream.com>, "Steven Wilcoxon" <swilcoxon@iqmktg.com> wrote: > Your two OUTER statements is what is causing child1*child2 records to be > returned. > > Can you do it as two seperate selects? Another solution is to UNION two > SELECT's, > one from each table with NULL's/defaults on the other tables fields. > > S.W. > > <strangiato@my-deja.com> wrote in message > news:7rjq8q$9op$1@nnrp1.deja.com... > > How to interpret rows from a mutli-table SELECT? > > > > I want to do a SELECT from a parent and it's children and use the > > resulting row as a basis for inserting into another table. > > > > Suppose the schemas are as follows : > > > > table parent > > > > parentkey > > > > parentdata > > > > > > > > table child1 > > > > parentkey > > > > child1key > > > > child1data > > > > > > > > table child2 > > > > parentkey > > > > child2key > > > > child2data > > > > If I do > > > > select * from parent a, outer child1 b,outer child2 c > > where a.parentkey = b.parentkey > > and a.parentkey = c.parentkey > > order bya.parentkey,b.child1key,b.child1data,b.child2key,b.child2data > > > > The number of rows returned seems to be number of parent rows x number > > of child1 rows x number of child2 rows > > > > Is this correct? > > > > Why does it do that? > > > > How do I interpret one of the rows returned in terms of which child row > > it came from? > > > > Thanks! > > > > > > > > Sent via Deja.com http://www.deja.com/ > > Share what you know. Learn what you don't. > > Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

Depending on the queries, data, system, and configuration, UNION's can be efficient. A UNION ALL in general is more efficient the a straight UNION (but you want to make sure that you don't duplicate data). On multi-processor boxes the SELECT's can be run in parallel very easily. A UNION (without the ALL) does add extra overhead because it does a distinct between all of the different SELECT's, but often that is the desired affect. Either way, a UNION with be much faster then you current SELECT becuase the number of rows produced will shrink. Two SELECT's instead of using a UNION simply acts like a UNION ALL but place more responsability in the application. The important thing is to test it out and go with what works best. My rule of thumb is 'when in doubt, let the DB Engine do the work' (that way it can parallise easier). S.W. <strangiato@my-deja.com> wrote in message news:7rleqv$ehu$1@nnrp1.deja.com... > That's a good suggestion. Thanks. > > Is UNION an efficient construct? > > In article > <204285895F88D1118FAC00A0C933CDDF348804@mail.silverstream.com>, > "Steven Wilcoxon" <swilcoxon@iqmktg.com> wrote: > > Your two OUTER statements is what is causing child1*child2 records to > be > > returned. > > > > Can you do it as two seperate selects? Another solution is to UNION > two > > SELECT's, > > one from each table with NULL's/defaults on the other tables fields. > > > > S.W. > > > > <strangiato@my-deja.com> wrote in message > > news:7rjq8q$9op$1@nnrp1.deja.com... > > > How to interpret rows from a mutli-table SELECT? > > > > > > I want to do a SELECT from a parent and it's children and use the > > > resulting row as a basis for inserting into another table. > > > > > > Suppose the schemas are as follows : > > > > > > table parent > > > > > > parentkey > > > > > > parentdata > > > > > > > > > > > > table child1 > > > > > > parentkey > > > > > > child1key > > > > > > child1data > > > > > > > > > > > > table child2 > > > > > > parentkey > > > > > > child2key > > > > > > child2data > > > > > > If I do > > > > > > select * from parent a, outer child1 b,outer child2 c > > > where a.parentkey = b.parentkey > > > and a.parentkey = c.parentkey > > > order by> a.parentkey,b.child1key,b.child1data,b.child2key,b.child2data > > > > > > The number of rows returned seems to be number of parent rows x > number > > > of child1 rows x number of child2 rows > > > > > > Is this correct? > > > > > > Why does it do that? > > > > > > How do I interpret one of the rows returned in terms of which child > row > > > it came from? > > > > > > Thanks! > > > > > > > > > > > > Sent via Deja.com http://www.deja.com/ > > > Share what you know. Learn what you don't. > > > > > > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't.