'Specific JOIN of 3 tables retrieving data

I have 3 tables in MySQL

USERS
uid
email
password
created

This is the table that have all records of all users

PHOTOS
iid
uid
file
thumb
uploaded

This is the table where all photos posted by each user are kept on

COMMENTS
cid
iid
uid
message
created

This is the table where all the comments on each photo by every user are kept on

What i want is a query that retrives me the amount (COUNT) of comments on every photo for every unique (DISTINCT) user. For example (the resulting output must be) :

comment_amount  by_user on_picture
3               1       1
2               2       1
2               1       2
5               2       2
6               2       3

so the output could tell us if it is formated something like (example)

3 comments posted by USER1 on PICTURE1
2 comments posted by USER2 on PICTURE1

2 comments posted by USER1 on PICTURE2
5 comments posted by USER2 on PICTURE2

6 comments posted by USER2 on PICTURE3

What i have so far is

SELECT 
users.uid
FROM users
INNER
    JOIN photos
    ON photos.uid = users.uid
INNER
    JOIN comments
    ON comments.iid = photos.iid

and here is the sandbox testing

http://sqlfiddle.com/#!2/955d5/1

But I don't know what to do next. Also any language besides MySQL should work as well!

mysqlsql


Solution 1:[1]

I would do the GROUP first:

SELECT  iid, uid, count(*)
FROM comments
GROUP BY  iid, uid

and after that join to the relevant tables.

Solution 2:[2]

Your query could be like:

SELECT users.uid AS UserId, count(comments.cid) AS CountComments
FROM users
INNER
    JOIN photos
    ON photos.uid = users.uid
INNER
    JOIN comments
    ON comments.iid = photos.iid
group by users.uid

You can test it

If you want to add an image id you can add photo.iid to group by 

SELECT users.uid AS UserId, count(comments.cid) AS CountComments, 
  photos.iid as PhotoId
  FROM users
INNER
    JOIN photos
    ON photos.uid = users.uid
INNER
    JOIN comments
    ON comments.iid = photos.iid
group by users.uid, photos.iid

Check it out

Solution 3:[3]

    SELECT 
    users.uid, photos.iid, count(*) nb
    FROM users
    INNER
    JOIN photos
    ON photos.uid = users.uid
INNER
    JOIN comments
    ON comments.iid = photos.iid

group by users.uid, photos.iid

Thanks for the sql fiddle link, i didn't know.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gidil
Solution 2
Solution 3 Loïc bcn

Related Questions