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).
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.
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)
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?