SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Addition to Join statement

    Hi all,

    I have a query that I use that is working a treat that basically checks 2 tables (one of images and one of users) and grabs users who have posted an image.

    The query is listed below:

    select tbl_users.user_id, tbl_users.user_display, max(tbl_images.image_day) as lastPost, max(tbl_images.image_date) as latestImage
    from tbl_users
    join tbl_images
    on tbl_users.user_id = tbl_images.user_id
    where tbl_images.image_day <= current_date
    and tbl_users.user_status =1

    group by tbl_users.user_id
    order by lastpost desc, latestImage desc

    My question is this, I have a 3rd table that lists people "I follow" can I query which simply has 2 fields - my user_id and their user_id - is there any way i can add this into the current query so that it adds another condition to say AND Where tbl_followers.user_id = tbl_uses.user_id.

    Then it will be in laymans terms (the first bit is above - the braketed bit i need to add) - Get the latest photos [from people I follow]

    No idea if any of that actually makes sense!

    cheers

    Mike

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,492
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Code:
    select 
        tbl_users.user_id
      , tbl_users.user_display
      , max(tbl_images.image_day) as lastPost
      , max(tbl_images.image_date) as latestImage
    from tbl_followers
    inner join tbl_users
    on tbl_followers.their_user_id = tbl_users.user_id
    inner join tbl_images
    on tbl_users.user_id = tbl_images.user_id
    where tbl_followers.my_user_id = 1  <-- change this value in your user_id vale
    and tbl_images.image_day <= current_date
    and tbl_users.user_status =1
    group by tbl_users.user_id
    order by lastpost desc, latestImage desc

  3. #3
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Worked an absolute treat!

    Thanks so much

  4. #4
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guido,

    Could you help with somthing else maybe please - I have a table of likes and a table of favourites - the link is img_id which exists in both.

    I want to count the number of likes and the number of favourites for each image and then order by the total - ie likes + favourites

    I tried the following but it comes unstuck with the group by and gives the wrong numbers. Any ideas?

    SELECT *, count(tbl_like.img_id) as Likes, count(tbl_favourite.img_id) as favourites, count(tbl_like.img_id) + count(tbl_favourite.img_id) as total
    FROM tbl_like
    INNER JOIN tbl_favourite
    ON tbl_like.img_id = tbl_favourite.img_id
    Group by tbl_like.img_id, tbl_favourite.img_id
    Order By total desc

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,492
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    You should start from the tbl_images table, and link the likes and favorites to each image in that table. Using a left join instead of an inner join, you'll also get the images that don't have any likes or favorites yet.

    Code:
    SELECT 
        tbl_images.img_id
      , COUNT(tbl_like.img_id) AS Likes
      , COUNT(tbl_favourite.img_id) AS favourites
      , COUNT(tbl_like.img_id) + COUNT(tbl_favourite.img_id) AS total
    FROM tbl_images
    LEFT OUTER JOIN tbl_like
    ON tbl_images.img_id = tbl_like.img_id
    LEFT OUTER JOIN tbl_favourite
    ON tbl_images.img_id = tbl_favourite.img_id
    GROUP BY tbl_images.img_id
    ORDER BY total desc

  6. #6
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again guido

    can i have it not select any that have 0 total as I have lots of images!

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,492
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Yes, use inner joins

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,492
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Ah, no, ignore that, it would give you only images that have likes AND favorites. If you want to extract images that have only likes OR only favorites as well, add a HAVING condition:
    Code:
    SELECT 
        tbl_images.img_id
      , COUNT(tbl_like.img_id) AS Likes
      , COUNT(tbl_favourite.img_id) AS favourites
      , COUNT(tbl_like.img_id) + COUNT(tbl_favourite.img_id) AS total
    FROM tbl_images
    LEFT OUTER JOIN tbl_like
    ON tbl_images.img_id = tbl_like.img_id
    LEFT OUTER JOIN tbl_favourite
    ON tbl_images.img_id = tbl_favourite.img_id
    GROUP BY tbl_images.img_id
    HAVING COUNT(tbl_like.img_id) + COUNT(tbl_favourite.img_id) > 0
    ORDER BY total desc

  9. #9
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Ah, no, ignore that, it would give you only images that have likes AND favorites. If you want to extract images that have only likes OR only favorites as well, add a HAVING condition:
    Code:
    SELECT 
        tbl_images.img_id
      , COUNT(tbl_like.img_id) AS Likes
      , COUNT(tbl_favourite.img_id) AS favourites
      , COUNT(tbl_like.img_id) + COUNT(tbl_favourite.img_id) AS total
    FROM tbl_images
    LEFT OUTER JOIN tbl_like
    ON tbl_images.img_id = tbl_like.img_id
    LEFT OUTER JOIN tbl_favourite
    ON tbl_images.img_id = tbl_favourite.img_id
    GROUP BY tbl_images.img_id
    HAVING COUNT(tbl_like.img_id) + COUNT(tbl_favourite.img_id) > 0
    ORDER BY total desc

    Excellent thanks Guido - I think we are close but it is adding the data up wrong - seems to be adding up much higher than I would expect - almost 3 times which is odd.

    for example 1 image has 3 likes and 3 favourites and im getting 9 for each and 18 as the total.

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,492
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Oops, yes, forgot about that. That's what you get if you don't test things
    Try this (not tested).
    Code:
    SELECT 
        tbl_images.img_id
      , COALESCE(likes.Likes, 0) AS Likes
      , COALESCE(favourites.Favourites, 0) AS Favourites
      , COALESCE(likes.Likes, 0) + COALESCE(favourites.Favourites, 0) AS Total
    FROM tbl_images
    LEFT OUTER JOIN 
      (SELECT 
           tbl_like.img_id
         , COUNT(tbl_like.img_id) AS Likes
       FROM tbl_like
       GROUP BY tbl_like.img_id
      ) AS likes
    ON tbl_images.img_id = likes.img_id
    LEFT OUTER JOIN 
      (SELECT 
           tbl_favourite.img_id
         , COUNT(tbl_favourite.img_id) AS Favourites
       FROM tbl_favourite
       GROUP BY tbl_favourite.img_id
      ) AS favourites
    ON tbl_images.img_id = favourites.img_id
    WHERE  COALESCE(likes.Likes, 0) + COALESCE(favourites.Favourites, 0) > 0
    ORDER BY Total desc

  11. #11
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Oops, yes, forgot about that. That's what you get if you don't test things
    Try this (not tested).
    Code:
    SELECT 
        tbl_images.img_id
      , COALESCE(likes.Likes, 0) AS Likes
      , COALESCE(favourites.Favourites, 0) AS Favourites
      , COALESCE(likes.Likes, 0) + COALESCE(favourites.Favourites, 0) AS Total
    FROM tbl_images
    LEFT OUTER JOIN 
      (SELECT 
           tbl_like.img_id
         , COUNT(tbl_like.img_id) AS Likes
       FROM tbl_like
       GROUP BY tbl_like.img_id
      ) AS likes
    ON tbl_images.img_id = likes.img_id
    LEFT OUTER JOIN 
      (SELECT 
           tbl_favourite.img_id
         , COUNT(tbl_favourite.img_id) AS Favourites
       FROM tbl_favourite
       GROUP BY tbl_favourite.img_id
      ) AS favourites
    ON tbl_images.img_id = favourites.img_id
    WHERE  COALESCE(likes.Likes, 0) + COALESCE(favourites.Favourites, 0) > 0
    ORDER BY Total desc
    Genius!

    Could you explain why the last one didnt work - what does COALESCE do?

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,492
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Let's take your example:
    for example 1 image has 3 likes and 3 favourites and im getting 9 for each and 18 as the total.
    So img_id 1 has the following data:
    tbl_image = 1 row
    tbl_like = 3 rows
    tbl_favourite = 3 rows

    Doing a join of these three tables by img_id means having 1 x 3 x 3 = 9 rows, with lots of duplicate values from the second and third table. I've resolved that by putting the count and group on the second and third table in their respective subqueries, so in the end I'll join three tables where the img_id is unique in each table, eliminating the duplication of data.

    I use COALESCE because doing a left join might mean that for a certain image id there are no likes and/or favourites, and in that case the value of the Likes and Favourites (results of the subqueries) are NULL. Using COALESCE, I take the value from the subquery if there is one <> NULL, and 0 if it is NULL

  13. #13
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guido,

    Sorry to resurrect an old thread but i wondered if you could help me add something to this....

    I want to limit the search to dates that are within the last 30 days

    ie img_date within 30 days of now()

    Thanks

  14. #14
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guido,

    Sorry to resurrect an old thread but i wondered if you could help me add something to this....

    I want to limit the search to dates that are within the last 30 days

    ie img_date within 30 days of now()

    Thanks


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
  •