CODEWITHSUNDEEP
phpmysqlsqlparent
SmootQ
SmootQ

Reputation: 2122

Parent Ordered by number of child tables

I have a tbl_user , which contains information about user, and I have a tbl_article, which contains articles + the ID of the tbl_user

We have a parent-child relation, because every user may have many articles, that's why I included user_id in the articles table.

I'd like to list the 10 users that have most articles... I've searched everywhere though I couldn't find it...I've thought about it , but in vain, I'm not good in SQL Queries.

Thank you in advance

Upvotes: 1

Views: 93

Answers (2)

JNK
JNK

Reputation: 65187

SELECT TOP 10
    UserID, COUNT(Article)
FROM tbl_User u
INNER JOIN tbl_Article a
    ON a.Userid = u.userid
GROUP BY userid
ORDER BY COUNT(article) DESC

All you need is a GROUP BY and a JOIN.

If there is a potential for users with 0 articles that you want to include, you should use a LEFT JOIN.

Optionally you can also COUNT(DISTINCT Article) if there is a concern about duplicates.

Upvotes: 2

MatBailie
MatBailie

Reputation: 86765

SELECT TOP(10)
  tbl_user.id,
  COUNT(tbl_article.user_id)
FROM
  tbl_user
LEFT JOIN
  tbl_article
    ON tbl_user.id = tbl_article.user_id
GROUP BY
  tbl_user.id
ORDER BY
  COUNT(tbl_article.user_id) DESC
LIMIT
  10

Depending on which RDBMS you use, you may need TOP(10) or LIMIT 10, etc. I included both so you can see, but only use the one that is used by your RDBMS ;)

Upvotes: 3

Related Questions