CODEWITHSUNDEEP
sqloracle-database
freeNinja
freeNinja

Reputation: 375

SQL: select query

I have these tables:

no is the order of comment written by a member on a photo

I want to select the name of the members who left more than 3 comments on the same photo.

I did this but it doesn't work:

SELECT name 
FROM MEMBER M, COMMENT C, PHOTO P
WHERE M.IdMember = C.IdMember = P.IdMember
  AND M.IdMember IN (SELECT IdMember 
                     FROM COMMENT
                     GROUP BY IdMember
                     HAVING COUNT(no) >= 3)

How can I change my query?

Upvotes: 0

Views: 64

Answers (2)

JeromeFr
JeromeFr

Reputation: 1928

In this case you don't need to join the Photo table because the only column you use from that table is IdPhoto and you already have that information in Comment table.

You can also directly join the subquery to the Member table without bringing the Comment table one more time.

As already said, you also have to add the IdPhoto in your group by clause, to count the number of post from the same Member on the same Photo

Have a try with this :

SELECT DISTINCT M.name
    FROM MEMBER M, (SELECT IdMember 
                 FROM COMMENT
                 GROUP BY IdMember, IdPhoto
                 HAVING COUNT(no) >= 3) C
    WHERE M.IdMember = C.IdMember;

Or with ANSI syntax :

SELECT DISTINCT M.name
    FROM MEMBER M
      INNER JOIN (SELECT IdMember 
                 FROM COMMENT
                 GROUP BY IdMember, IdPhoto
                 HAVING COUNT(no) >= 3) C
    ON M.IdMember = C.IdMember;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Your query is close, but you need to aggregate in the Comment table by both the member and the photo. Try this:

SELECT name FROM MEMBER M, COMMENT C, PHOTO P
WHERE M.IdMember = C.IdMember = P.IdMember
AND M.IdMember IN
(
    SELECT IdMember
    FROM Comment
    GROUP BY IdMember, IdPhoto
    HAVING COUNT(*) >= 3
)

But really I would rather write this query using explicit joins everywhere:

SELECT name
FROM MEMBER m
INNER JOIN COMMENT c1
    ON m.IdMember = c1.IdMember
INNER JOIN PHOTO p
    ON c1.IdMember = p.IdMember
INNER JOIN
(
    SELECT IdMember
    FROM Comment
    GROUP BY IdMember, IdPhoto
    HAVING COUNT(*) >= 3
) c2
    ON c1.IdMember = c2.IdMember

Upvotes: 1

Related Questions