joining several tables

[james]

Hi,

I would like to get all the records from 9 tables that have the same
field value in one field (it is a unique field)that is shared by all the
tables. Would this
method of joining work:

select * from table 1, table 2, table 3 where table 1.com_field =
table 2.com_field and table 2.com_field = table 3.com_field

(I left off the rest of tables to keep this message small.)If not, how can I
do it?

thanks
James__________ _______________ _______________ _______________ ___________
james ICQ#: 4617005 Current ICQ status: + More ways to contact me i See more
about me: _______________ _______________ _______________ _______________ ______

[Mark A]

"james" <th*****@hotmai l.com> wrote in message
news:ZO******** *********@newsr ead4.news.pas.e arthlink.net...

Hi,

I would like to get all the records from 9 tables that have the same
field value in one field (it is a unique field)that is shared by all the
tables. Would this
method of joining work:

select * from table 1, table 2, table 3 where table 1.com_field =
table 2.com_field and table 2.com_field = table 3.com_field

(I left off the rest of tables to keep this message small.)If not, how can I do it?

thanks
James

Yes, I think that would work. I assume you would also have: where table
1.com_field = 'xxxxxxxx.' Also, be careful about select *. Try to only
select the columns you really need.

If the tables are all the same columns (don't have to be the same column
names) then you might try a UNION ALL with the same where clause in each
select. This would be more efficient.

[Joerg Ammann]

"Mark A" <ma@switchboard .net> wrote in message news:<Bj******* ***********@new s.uswest.net>.. .

"james" <th*****@hotmai l.com> wrote in message
news:ZO******** *********@newsr ead4.news.pas.e arthlink.net...

Hi,

I would like to get all the records from 9 tables that have the same
field value in one field (it is a unique field)that is shared by all the
tables. Would this
method of joining work:

select * from table 1, table 2, table 3 where table 1.com_field =
table 2.com_field and table 2.com_field = table 3.com_field

(I left off the rest of tables to keep this message small.)If not, how can

I

do it?

thanks
James

Yes, I think that would work. I assume you would also have: where table
1.com_field = 'xxxxxxxx.' Also, be careful about select *. Try to only
select the columns you really need.

If the tables are all the same columns (don't have to be the same column
names) then you might try a UNION ALL with the same where clause in each
select. This would be more efficient.

be aware that the resultset (from the 2 above statements) will not be the same.

assume each table has 5 columns and 1000 rows (rows with the desired value).

select * from tab1, tab2, tab3 ... tab9 where .....
will give you a resultset with 1000 rows long and 9*5 columns wide.

tab1.c1 tab1.c2 tab1.c3 tab1.c4 tab1.c5 . . . . tab9.c5
------- ------- ------- ------- ------- -------
....
select * from tab1 where ....
UNION ALL
select * from tab2 where ....
....
....
UNION ALL
select * from tab9 where ....

will give you 9000 rows by 5 columns
reading your email, it's not clear - at least for me - which do you wanna
(maybe an example would help)

jörg

[james]

Thanks, and here is an example. What I am trying to do, is prove that all 9
tables with the same key fields have the same com_field value. The com_field
will be added to all the tables during a database conversion, it will be a
numeric field generated using DB2 Identity feature. The results I need to
see would look something like this:

table1.key field 1
table 1. key field 2
table 1. com_field

table2. key field 1
table 2 .key field 2
talbe 2 . com_field

table 3 .key field 1
table 3 .key field 2
table 3 . com_field

.....

All the field values would be identical. I don't need to see the results of
the entire query, several samples should do. Hope this makes it more
understandable!

James