Addition to Join statement

[billybrag]

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

[guido2004]

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

[billybrag]

Worked an absolute treat!

Thanks so much

[billybrag]

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

[guido2004]

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.

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

[billybrag]

Thanks again guido

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

[guido2004]

Yes, use inner joins

[guido2004]

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:

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

[billybrag]

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:

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.

[guido2004]

Oops, yes, forgot about that. That's what you get if you don't test things
Try this (not tested).

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

[billybrag]

Oops, yes, forgot about that. That's what you get if you don't test things
Try this (not tested).

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?

[guido2004]

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

[billybrag]

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

[billybrag]

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