CODEWITHSUNDEEP
mysql
R Wri
R Wri

Reputation: 302

How to create the proper mysql query when joining multiple tables

I am looking for a bit of help creating a query I am going to use to display some statistics. I am not super familiar with mysql, so it has been tricky to come up with something.

I have 3 tables that contain information about an organization. Here are the tables:

   Organizations                         Teams
+-----------------+          +--------------------------+
| ORGID | ORGNAME |          | TEAMID| ORGID | TEAM NAME|
+-----------------+          +--------------------------+
|   1   |  ORG A  |          |   1   |   1   |  TEAM A  |
+-----------------+          +--------------------------+
                             |   2   |   1   |  TEAM B  |
                             +--------------------------+
   Team Members
+-----------------------------------------------------------------------+
| MEMBERID | TEAMID | MEMBER NAME | FIRST ACTIVE | LAST ACTIVE | ACTIVE |
+-----------------------------------------------------------------------+
|    1     |    1   |     BOB     |  2019-05-01  |  2019-06-05 |   N    |
+-----------------------------------------------------------------------+
|    2     |    1   |    JANE     |  2019-06-01  |  2019-07-09 |   Y    |   
+-----------------------------------------------------------------------+
|    3     |    2   |     PAT     |  2019-07-01  |  2019-07-09 |   Y    |   
+-----------------------------------------------------------------------+

The query I am trying to make will display the Teams, how many members they have and how my members are not active. So my expected will look something like:

+----------------------------------------+
|  TEAM  | MEMBER COUNT | INACTIVE COUNT |
+----------------------------------------+
| TEAM A |      2       |        1       |
+----------------------------------------+
| TEAM B |      1       |        0       |
+----------------------------------------+

Getting the results for each column is trivial, but combining them is where I am running into issues.

The initial query I have came up with is:

SELECT teams.teamname AS teamname, COUNT(DISTINCT teammembers.memberid) AS members, COUNT(teammembers.active) AS active FROM teams
LEFT JOIN teammembers ON teams.teamid = teammembers.teamid
LEFT JOIN organizations ON teams.orgid = organizations.orgid
WHERE organizations.orgname = 'ORG A'
AND teammembers.active = 'N'
GROUP BY teams.teamname;

This query returns results that are not accurate, it is just counting the results where not active is = "N", which I can see.

I am not sure if I am not joining these tables correctly, or if I should be using a subquery. Hopefully someone can help guide me to the right result. I can provide more info I am I missing anything.

Upvotes: 0

Views: 25

Answers (1)

forpas
forpas

Reputation: 164099

You must remove the condition AND teammembers.active = 'N' from the WHERE clause because this will not count all members buy only the inactive:

SELECT 
  t.teamname AS teamname, 
  COUNT(DISTINCT m.memberid) AS members_count, 
  SUM(m.active = 'N') AS inactive_count 
FROM teams t
LEFT JOIN teammembers m ON t.teamid = m.teamid
LEFT JOIN organizations o ON t.orgid = o.orgid
WHERE o.orgname = 'ORG A'
GROUP BY t.teamname;

With SUM(m.active = 'N') you count the inactive memebers.
Also I don't think that it is necessary to use DISTINCT for COUNT(DISTINCT m.memberid) (although I kept it). Is there a case for a team member with 2 rows for the same team?

Upvotes: 1

Related Questions