By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,701 Members | 1,955 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,701 IT Pros & Developers. It's quick & easy.

joining several tables

P: n/a
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: __________________________________________________ ________________
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"james" <th*****@hotmail.com> wrote in message
news:ZO*****************@newsread4.news.pas.earthl ink.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.
Nov 12 '05 #2

P: n/a
"Mark A" <ma@switchboard.net> wrote in message news:<Bj******************@news.uswest.net>...
"james" <th*****@hotmail.com> wrote in message
news:ZO*****************@newsread4.news.pas.earthl ink.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
Nov 12 '05 #3

P: n/a
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
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.