SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: My first join

  1. #1
    He's No Good To Me Dead silver trophybronze trophy stymiee's Avatar
    Join Date
    Feb 2003
    Location
    Slave I
    Posts
    23,424
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    My first join

    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):

    Code:
    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.

  2. #2
    He's No Good To Me Dead silver trophybronze trophy stymiee's Avatar
    Join Date
    Feb 2003
    Location
    Slave I
    Posts
    23,424
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    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.

    Code:
    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'


Bookmarks

Posting Permissions

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