CODEWITHSUNDEEP
mysqlsql
Constantly Groovin'
Constantly Groovin'

Reputation: 417

How do I retrieve parent information for a child query?

So I have 2 tables communities and communities_members. In the table communities_members there is a like to the communities table i.e if there are 2 communities : community 1 and community 2 then if I join community 1 there will now be a communities_members where communities id = 1.

So now I want to execute a query where it looks at all the communities and sorts them by number of members.

The problem I run into is this :

SELECT * FROM communities ORDER BY (SELECT COUNT(*) FROM community_members WHERE community_id=?)

where I put the question mark is where I want the communities id found in the parent query goes in the child one i.e If it starts by examining the community 1 (with a community_id of 1) then I want to to count all of the rows in community_members where community_id is equal to the first community_id.

Please help!

Upvotes: 0

Views: 31

Answers (1)

Fahmi
Fahmi

Reputation: 37493

If I understand properly then you can do using a join

SELECT c.name,count(*) as total_member
FROM communities c join community_members cm on c.community_id=cm.community_id
group by c.name
order by total_number desc

Upvotes: 2

Related Questions