SQL - get results from table 1, but count all matching results from 2, 3 and 4
I am trying to print a list of 20 articles online, along with the number of comments, photos and videos for each article. They all share a box article_id
, but not every article has a comment, photo, or video.
My working query: (no counts)
SELECT a.article_id, a.title, a.published, a.description, a.status, ad.firstname, ad.lastname FROM blog_articles AS a
LEFT JOIN admins AS ad ON a.author_id = ad.admin_id *** grabs article author
WHERE a.status = 'Online' ORDER BY a.published DESC LIMIT 0, 20
My attempt (with counters)
SELECT COUNT(comment_id) AS tot_comments, COUNT(photo_id) AS tot_photos,
COUNT(video_id) AS tot_videos, a.article_id, a.title, a.published, a.description,
a.status, ad.firstname, ad.lastname
FROM blog_articles AS a
LEFT JOIN blog_comments AS ac USING (article_id)
LEFT JOIN photos AS p USING (article_id)
LEFT JOIN videos AS v USING (article_id)
LEFT JOIN admins AS ad ON a.author_id = ad.admin_id
WHERE a.status = 'Online'
GROUP BY a.article_id
ORDER BY a.published DESC LIMIT 0, 20
But this is acting strange. I am getting correct articles and text data, but the count is being replayed! If the article does have 18 comments, 0 photos and 1 video, my displayed results say 18 comments, 0 photos and 18 videos. If the article has 0 comments, 6 photos and 1 video, my results are 0 comments, 6 photos and 6 videos!
I'm close to making it work, my knowledge of joining is rusty, my brain is hurting, please can anyone spare me my misery and stumble upon the finish line? I can see what is going on, but I cannot work out how to solve it.
This is what I want to display for every record in my table blog_articles
:
Article title: This title | Comments: 42 | Photos: 20 | Videos: 2
source to share
I think your best bet is to dump tables
SELECT a.title, c.cnt as comments, p.cnt as photos, v.cnt as videos
FROM blog_articles a
INNER JOIN admins ad
ON a.author_id = ad.admin_id
LEFT OUTER JOIN (
SELECT article_id, COUNT(comment_id) as cnt
FROM blog_comments
GROUP BY article_id) c
ON a.article_id = c.article_id
LEFT OUTER JOIN (
SELECT article_id, COUNT(photo_id) as cnt
FROM photos
GROUP BY article_id) p
ON a.article_id = p.article_id
LEFT OUTER JOIN (
SELECT article_id, COUNT(video_id) as cnt
FROM videos
GROUP BY article_id) v
ON a.article_id = v.article_id
WHERE a.status = 'Online'
GROUP BY a.article_id
ORDER BY a.published DESC LIMIT 0, 20
Just because I couldn't let it go, have a look at this SQL script which shows the execution plans and times of both the view and the counting of various parameters. Both provide the same data, but in this example the views are 0ms versus 1ms for the counts. It would be interesting to see these queries run on some real data, but in my experience the resulting tables give performance that cannot be calculated.
source to share
Haven't tried but try
SELECT COUNT(comment_id) AS tot_comments, COUNT(photo_id) AS tot_photos,
COUNT(video_id) AS tot_videos, a.article_id, a.title, a.published, a.description,
a.status, ad.firstname, ad.lastname
FROM blog_articles AS a
LEFT JOIN blog_comments AS ac ON ac.article_id = a.article_id
LEFT JOIN photos AS p ON p.article_id = a.article_id
LEFT JOIN videos AS v ON v.article_id = a.article_id
LEFT JOIN admins AS ad ON a.author_id = ad.admin_id
WHERE a.status = 'Online'
GROUP BY a.article_id
ORDER BY a.published DESC LIMIT 0, 20
source to share