-1

Two tables: MEMBER, GROUP.

I want to get name and email from MEMBER table, and I want get the number of GROUPS a member is registered in.

GROUP has a memberId as FK. So memberId = 1 could have many groupID.

I want a single SQL query to gather info on all the members and how many groups they're in.

Adam Wenger
  • 17,100
  • 6
  • 52
  • 63
user1121487
  • 2,662
  • 8
  • 42
  • 63

2 Answers2

0
SELECT name, email, count(`GROUP`.id)
FROM `MEMBER` INNER JOIN `GROUP` ON `GROUP`.memberId = MEMBER.memberId
GROUP BY MEMBER.memberId
Buggabill
  • 13,726
  • 4
  • 42
  • 47
Marlin Pierce
  • 9,931
  • 4
  • 30
  • 52
  • Have you tested this? I don't have mysql here, but the `GROUP BY` doesn't look like it will work. – Adam Wenger Oct 17 '12 at 18:27
  • ~Adam, the edit by ~Buggabill fixes it. GROUP BY is a reserved word in SQL, but the question used GROUP as a table name. The backquotes of the table name may fix the SQL. Better would be to never use an SQL reserve word as a table name (even in an example :) ) – Marlin Pierce Oct 17 '12 at 19:16
  • I can run it but it gives no results at all. – user1121487 Oct 17 '12 at 19:20
  • @MarlinPierce My concern is that you have values in the `SELECT` that are not in the `GROUP BY`. This is not allowed in SQL Server, and I was doubting this was possible with MySql either. – Adam Wenger Oct 17 '12 at 19:23
  • Same as comment above: It displays data but ONLY if member is registered in at least one group. I must show members with 0 groups as well. – user1121487 Oct 17 '12 at 19:45
  • @AdamWenger: It is allowed in MySQL: [Why does MySQL add a feature that conflicts with SQL standards?](http://stackoverflow.com/questions/7594865/why-does-mysql-add-a-feature-that-conflicts-with-sql-standards) – ypercubeᵀᴹ Oct 17 '12 at 20:18
  • ~user1121487 to get members with no groups change INNER JOIN to LEFT OUTER JOIN. – Marlin Pierce Oct 17 '12 at 20:49
  • ~AdamWenger You are right. That's the way I learned SQL, after DB2 kept beating me over the head with putting all the non-aggregated fields in the select in the group by. However, it is predictable what the group by needs to be from the select, so I'm not surprised a later database vendor figures out what to do for me. :) – Marlin Pierce Oct 17 '12 at 20:52
  • @ypercube Thanks for the information and links in your answer. – Adam Wenger Oct 18 '12 at 03:00
0

I'm assuming the primary key of MEMBER is memberId. Try this

SELECT m.name, m.email, count(g.memberId )
FROM `MEMBER` m 
LEFT JOIN `GROUP` g ON g.memberId = m.memberId
GROUP BY m.memberId
Moyed Ansari
  • 8,436
  • 2
  • 36
  • 57