Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005

    Unanswered: need help with join

    I'm not a DB admin, just a sys admin needing to pull data from two tables for a cron-executed report. DB2 9.5. Wrapping query with bash script (suse linux).

    Table 1:
    device_status -- the value in this small table is unambiguous and should be used as a primary key in the join.
    device_name -- only other useful field in the table.
    status_time_stamp -- sometimes useful, but not in this query.

    Table 2:
    (other useless fields)

    Here's where the problem lies. I don't see a clear primary key from table 2 to match to table 1. I think I need to select just a certain code_type from that table. If I whittled the table down to that one code_type then the code_number suddenly becomes unambiguous and matches up nicely with the device_status from table1. Otherwise that code_number is repeated over & over for 10000 different code_numbers and doesn't work as an unambiguous primary key.

    1. So how do I construct a complex select/JOIN statement for these two tables?
    2. Should I first do a select statement on table 2, export that to a temp table where the code_number can be an unambiguous primary key to join against device_status primary key from table 1?
    3. Is there some other way to handle this?

    If I followed #2 I'd do something like:

    select code_number,message from table1 where code_type=123...

    <somehow insert that into a newly created table3 which I've never done but could figure out the syntax on how to do that....> (remember, that code_number
    then matches up to the device_status value from table1)
    then ---
    select table1.device_name, table3.message from table1, table3 JOIN table1 ON (table1.device_status = table3.code_number) WHERE <set up whatever specific device conditions I'm looking for...>

    I know I probably butchered the syntax....Can I join the results of a subquery to a master query without making a whole new table?

    Ideas? Thoughts? Thanks for your help.

  2. #2
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    select t1.device_name,t2.message
       from table2 t2 
    inner join table1 t1
         on t1.device_status = t2.code_number
        and <set up whatever specific device conditions ....
    where t2.code_type=123...

  3. #3
    Join Date
    Nov 2005
    that worked. thanks so much.

Posting Permissions

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