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]
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 [B][COLOR="#FF0000"]<-- change this value in your user_id vale[/COLOR][/B]
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
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
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
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.
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
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
