SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    PT
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Verify if There Are Any Rows In a 2nd Left Joined Table

    Hello!

    I need some help resolving this, as I haven't found a resolution yet, but I'm almost sure that this can be done.

    I have the following query (in this example for site with ID 15):

    Code MySQL:
    SELECT 
        l.`site_id`, 
        l.`userstring`, 
        l.`traffic`, 
        l.`time`
    FROM 
        `traffic_log` l
    LEFT JOIN
        `top` t
    ON 
        l.`site_id` = t.`id`  
    WHERE 
        t.`approved` > 0
    AND
        l.`site_id` = 15   
    ORDER BY 
        l.`time` 
    DESC LIMIT 14;

    Now:
    - traffic_log table can have multiple results of the same site ID.
    - top has the unique site ID of all sites.

    So in the above example I'm selecting site details from table top and grabbing all it's traffic logs from table traffic_log.

    So far so good, the problem comes if there aren't any logs in traffic_log table for a specific site ID.

    I've temporary make a workaround by calling a first query to verify if there is any data in traffic_log, before I call the above query.
    Code MySQL:
    SELECT 1 FROM `traffic_log` WHERE `site_id` = 15 ORDER BY `id` LIMIT 1;
    If there is at least one result I can safely call the LEFT JOIN query.

    ...but I'm a friend of simplicity and performance, so there must be a way to make all that with only one MySQL query, I just don't know how.

    Can someone please help?

    Many thanks in advance!!
    Yesterday is history. Tomorrow is a mystery.
    Today is a gift, that's why it's called the Present.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Switch the order of the tables... if you want all the rows from top, then select from top, and left outer join the log table. It'll have a row for every row in top, and NULLs for the columns in the log table if there are no entries.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    PT
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much for your reply, Dan.

    I'll try that and come back with the result...
    Yesterday is history. Tomorrow is a mystery.
    Today is a gift, that's why it's called the Present.


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
  •