SQL select query to get total unique entries from two foreign key tables based on a column value-CodePudding

I've got three tables as shown below - ideally it wouldn't be laid out like this but currently no power change it.

Team              User                          Member

ID | Name         ID | TeamId | Email            ID | TeamId | Email
----------
1  | Team A       1  |  1     | [email protected]      1  |  1     | [email protected]
2  | Team B       2  |  1     | [email protected]      2  |  1     | [email protected]
3  | Team C                                      3  |  1     | [email protected]

I need to be able to get the combined count of users and members in each team, uniquely based on their email address. So for example, Team A would have a unique count of combined members and users of 3.

An entry may exist in either the user table OR the member table, or in both for each email. The outcome of the query would be TeamName and TotalUsers.

Any help with this type of query would be greatly appreciated.

CodePudding user response：

Use UNION to collect all the distinct combinations of team ids and emails from User and Member and do a LEFT join of Team to that resultset and aggregate:

SELECT t.id, t.name,
       COUNT(email) count
FROM Team t
LEFT JOIN (
  SELECT teamid, email FROM User
  UNION
  SELECT teamid, email FROM Member
) e ON e.teamid = t.id
GROUP BY t.id;

CodePudding user response：

you can UNION members and User table, so that duplicates would be removed

And then join it to the temas table

SELECT 
   t1.Name, COUNT(DISTINCT Email)
FROM 
   Team  t1
   JOIN
   ( SELECT TeamId , Email FROM User
   UNION SELECT TeamId , Email FROM Member) t2 ON t2.TeamId = t1.ID
GROUP BY t1.Name

CodePudding user response：

ID is primary key in three tables. Input data is available for Team A for now.

SELECT count(member.email) as TotalUsers,team.name as TeamName FROM user left JOIN member ON user.id=member.teamid left join team ON member.id=team.id

Result