My first join

[stymiee]

Hopefully I will explain this well for you. I have three tables which hold information that I need to some calculations with and then display. Nothing really to fancy to be honest with you.

Table 1 - accounts: need to select 'control_no', 'dba', 'date_added', 'timestamp', and 'status_id'

Table 2 - channels: need to select 'channel_name' (each channel_name has a channel_id which is the same 'channel_id' in the accounts table)

Table 3 - history: need to select 'timestamp' (there may be, and usually will be, multiple records in here for each account. I only need the top one (most recent). The value held in this field is a Unix timestamp.)

I will need to subtract the timestamp selected from the history table from the one selected from the accounts table.

This what I have so far (meaning it works):

SELECT accounts.control_no, accounts.dba, accounts.date_added, accounts.status_id, channels.channel_name 
FROM accounts 
LEFT JOIN channels USING (channel_id) 
WHERE accounts.status_id='20' OR accounts.status_id='40'

This takes care of joining the channels table with the accounts table. But so far all of my efforts to get the data from the history table included in this query has failed.

[stymiee]

Holy cow I got it! Here's the code for those who are curious. If there is a better way of doing this, however, feel free to chime in.

SELECT accounts.control_no, accounts.dba, accounts.date_added, accounts.status_id, channels.channel_name, (history.timestamp-accounts.date_added) AS difference
FROM accounts 
LEFT JOIN channels ON accounts.channel_id=channels.channel_id 
LEFT JOIN history ON accounts.control_no=history.control_no
WHERE accounts.status_id='20' OR accounts.status_id='40'