Professional Web Applications Themes

joining several tables - IBM DB2

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: __________________________________________________ ________________...

  1. #1

    Default joining several tables

    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: __________________________________________________ ________________


    james Guest

  2. #2

    Default Re: joining several tables


    "james" <com> wrote in message
    news:ZOKjb.3688$news.pas.earthlink.net... 


    Yes, I think that would work. I assume you would also have: where table
    1.com_field = 'xx.' 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.


    Mark Guest

  3. #3

    Default Re: joining several tables

    "Mark A" <net> wrote in message news:<BjLjb.2492$uswest.net>... 
    > I 
    >
    > Yes, I think that would work. I assume you would also have: where table
    > 1.com_field = 'xx.' 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.[/ref]

    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
    Joerg Guest

  4. #4

    Default Re: joining several tables

    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


    james Guest

Similar Threads

  1. Joining three tables
    By DettCom in forum Coldfusion Database Access
    Replies: 4
    Last Post: July 19th, 06:12 PM
  2. Joining Tables
    By AMARITA in forum Coldfusion Database Access
    Replies: 4
    Last Post: September 20th, 02:02 PM
  3. Joining tables, Please help
    By Student_bob in forum Coldfusion Database Access
    Replies: 4
    Last Post: April 19th, 08:49 AM
  4. joining 3 tables?
    By siti_nana in forum Dreamweaver AppDev
    Replies: 2
    Last Post: February 27th, 08:16 AM
  5. Joining Tables Across Databases
    By Wilkinson, Marcus in forum Informix
    Replies: 3
    Last Post: October 31st, 11:04 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139