CODEWITHSUNDEEP
mysqlsql
Sally
Sally

Reputation: 35

How do I finish of this SQL query

It works but what I would like is to list username, first name, last name not all but I've tried JOIN and isn't seeming to work. Any ideas? Thanks!

MY DB:

http://gyazo.com/eb13cd68440d20719ce0783018cb9828

Upvotes: 0

Views: 134

Answers (2)

scragar
scragar

Reputation: 6824

  SELECT
      M.Username,
      M.first_name,
      M.Last_name,
      COUNT(1) AS num_comments

  FROM members AS M
  INNER JOIN comments AS C
      ON C.memberID = M.memberID

  GROUP BY
      C.memberID
  ORDER BY COUNT(1) DESC
  LIMIT 1

This matches the Member to all their comments, groups by the member to get the count of comments for the users, orders by the count starting highest first, then returns the first result.

Upvotes: 1

daZza
daZza

Reputation: 1689

Instead of selecting * (ALL) just use SELECT table.username, table.firstname, table.lastname [...].

You can leave out the table. if all information is stored in your comments table. If not, adjust accordingly. In that case you'll also need to Join the comments table with the table where the rest of the information is stored.

Edit:

SELECT m.username, m.first_name, m.last_name FROM members m, comments c WHERE m.MemberID = c.MemberID AND c.author = (select max(author) from comments)

Upvotes: 1

Related Questions