SQL: select query

Question

I have these tables:

Member (IdMember, name, nbFollower)
Follower (IdMember, IdFollower, date)
Friend (IdMember, IdFriend)
Photo (IdPhoto, datePhoto, IdMember)
Comment (IdPhoto, IdMember, no, content)
Like (IdPhoto, IdMember)

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?

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (25 years ago) and its use is discouraged — marc_s
– marc_s, Commented Feb 21, 2017 at 5:59

Tim Biegeleisen · Accepted Answer · 2017-02-21 04:36:15Z

1

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

answered Feb 21, 2017 at 4:36

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JeromeFr · Accepted Answer · 2017-02-21 15:40:07Z

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;

Collectives™ on Stack Overflow

SQL: select query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related