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:
code_type
code_number
message
(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.

Or:
Dave

that worked. thanks so much.