Sql help!

[richardycc]

I am trying to create a query to get the results from a few tables, but I think the best way to do this is to create a temp table, then query this temp table. The data structures are like this:

Table 1
Columns: Customer_name, record_key, some other columns that I don’t care
Table 2
Columns: Record_key, table1_record_key, some other columns
Table 3
Record_key, table2_record_key, details, some other columns

So I need to either come up with a query to give me the customer name from table 1 and details from table 3.
I have a query with some nested sub selects, it works, but it will only return me the info from table 3 without the customer_name, because table 3 doesn't have that info.
So I think I can create a temp and insert the data from each table individually.

I will first create the temp table with columns customer_name, table1_record_key, table2_record_key, table3_record_key, details

And insert the 2 columns from table 1 into this temp table, but what does the 2nd query look like to insert the data from table 2 where table1_record_key = record_key?

 

[Jeraden]

Why can't you just join all 3 tables together?

select *
from table1, table2, table3
where table2.table1_record_key = table1.record_key
and table3.table2_record_key = table2.record_key

Switch to outer joins if table1 records don't necessarily need to have table2/table3 records.

 

[Ken g6]

SELECT table1.customer_name, table3.* FROM table1 INNER JOIN table2 ON table1.record_key = table2.table1_record_key INNER JOIN table3 ON table2.record_key = table3.table2_record_key

Very similar to Jeraden's except I used INNER JOIN (same thing really) and selected only the columns you said you wanted.

 

[richardycc]

yeah I figured it out, I some how took the hard way, joining the 3 tables should work, except in reality I am joining 5 tables, but it should work the same way. Thanks!