#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    5
    Rep Power
    0

    Trying to join multiple tables


    I am trying to join multiple tables and get an output listing. I want to get the last time a memo was modified.

    The Code I am currently using is below:

    [Code]

    if(count($left_a)) {

    $left_qtx = implode(' ', $left_a);
    }

    if(count($where_a)) {

    $where_qtx = "WHERE " . implode(' && ', $where_a);
    }

    $bid_qtx =
    "SELECT b.login, b.job_id, b.bid_id, b.step, b.when_sent, b. hosp_notify, ih.name, l.first_name, l.last_name, j.vet_req, mem.sender, mem.modified
    FROM jobs_bid AS b
    LEFT JOIN jobs_web AS j ON (b.job_id=j.id)
    LEFT JOIN info_hosp AS ih ON (j.hosp_id=ih.id)
    LEFT JOIN login AS l ON (b.login=l.login)
    LEFT JOIN jobs_bid_memo as mem on (b.bid_id=mem.bid_id)
    $left_qtx $where_qtx GROUP BY b.bid_id;";

    This creates the listing but it does not consistently show the latest mem.modified date, sometimes it does sometimes it doesn't. I would like to keep the GROUP BY b.bid_id for easiness but if that is not possible, then I can change it. I also want to limit only one row per bid_id.

    Thank you for your assitance in advance.
  2. #2
  3. No Profile Picture
    Dissident
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2003
    Location
    New York
    Posts
    1,671
    Rep Power
    52
    it's not showing you the memo date because you're using left joins.

    A left join will show all values from the left table (jobs bid) regardless of whether or not they have information associated from the right table (memos)

    In your example, it's grabbing all of the jobs bid data, then if it finds any associated memo info, it tacks it on.

    An inner join will show all values from the left table that have associated values in the right table.

    If you use an inner join, it will only show you jobs bid that have memos associated with them.

    A right join is just like a left join, except that the polarity of the tables is reversed, so it will show all values from the right table (memos) regardless of whehter or not they have information associated from the left table (jobs bid)

    So let's see some examples:

    PHP Code:
    jobs_bid
    ----------
    id ... code ... client
    ... 1111 ... foo
    ... 1112 ... bar

    memos
    ---------
    id ... job_id ... contents
    1  
    ... 1      ... 'foo is mean on the phone'
    2  ... 5      ... 'bar is nice on the phone'

    SELECT b.clientm.contents FROM jobs_bid b
        LEFT JOIN memos m ON b
    .id m.job_id

    returns 

    client 
    ... contents
    ---------------------
    foo  ... 'foo is mean on the phone'
    bar  ... ''

    SELECT b.clientm.contents FROM jobs_bid b
        RIGHT JOIN memos m ON b
    .id m.job_id

    returns 

    client 
    ... contents
    ---------------------
    foo ... 'foo is mean on the phone'
        
    ... 'bar is nice on the phone'

    SELECT b.clientm.contents FROM memos m 
        INNER JOIN jobs_bid b ON b
    .id m.job_id

    returns 

    client 
    ... contents
    ---------------------
    foo ... 'foo is mean on the phone' 

IMN logo majestic logo threadwatch logo seochat tools logo