How to get the output as following by combining data from 3 different tables-CodePudding

so I have 3 tables in my db, where all 3 tables contains a column which have similar data, but name of that column is different on all the 3 tables, below is an example.

Ban Table

user_id	ban_user_id	ban_date	reason	end_date
1300	1	xyz	xyz	xyz
32	1	xyz	xyz	xyz
43	2	xyz	xyz	xyz

Reports Table

user_id	last_modified_user_id	report_date	reason	end_date
1300	1	xyz	xyz	xyz
32	2	xyz	xyz	xyz
43	2	xyz	xyz	xyz

Warning Table

user_id	warning_user_id	warning_date	reason	end_date
1300	1	xyz	xyz	xyz
32	2	xyz	xyz	xyz
43	3	xyz	xyz	xyz

Now I want to fetch data by combining these 3 tables, where ban_user_id, last_modified_user_id, and warning_user_id contains the data of staff member who took the actions, so i want to group the data by the staff id.

The output i am looking for is as follows:

staff_id	total_reports	total_bans	total_warnings
1	1	2	1
2	2	1	1
3	0	0	1

where it is counting the data for each table by grouping the 2nd column, ban_user_id, last_modified_user_id, warning_user_id respectively. And than combining the data.

I tried things with UNION All and stuffs, but it didn't work out.

Thankyou in advance for your help

CodePudding user response：

Use UNION ALL for all 3 tables and then aggregate:

SELECT staff_id,
       COUNT(report) AS total_reports,
       COUNT(ban) AS total_bans,
       COUNT(warning) AS total_warnings
FROM (
  SELECT last_modified_user_id AS staff_id, 1 AS report, null AS ban, null AS warning FROM Reports
  UNION ALL
  SELECT ban_user_id, null, 1, null FROM Ban
  UNION ALL
  SELECT warning_user_id, null, null, 1 FROM Warning
) t
GROUP BY staff_id;

Or:

SELECT staff_id,
       SUM(report) AS total_reports,
       SUM(ban) AS total_bans,
       SUM(warning) AS total_warnings
FROM (
  SELECT last_modified_user_id AS staff_id, 1 AS report, 0 AS ban, 0 AS warning FROM Reports
  UNION ALL
  SELECT ban_user_id, 0, 1, 0 FROM Ban
  UNION ALL
  SELECT warning_user_id, 0, 0, 1 FROM Warning
) t
GROUP BY staff_id;

See the demo.

CodePudding user response：

You can use Table joins (Inner/outer/left/right) to get the data instead of union. I'm assuming the staff_id is the equivalent of user_id column as you haven't mentioned anything about that, so your script will look something like this:

SELECT W.user_id AS staff_id, 
    B.ban_user_id, 
    R.last_modified_user_id, 
    W.warning_user_id
FROM Warning AS W
LEFT JOIN Reports AS R on R.user_id = W.user_id
LEFT JOIN Ban AS B on B.user_id = W.user_id
group by W.user_id