MySQL Get row from joined table only if 1 such type exists-CodePudding

Trying to get my head around a maybe really simple query. It concerns two tables that I'm joining, but need to filter its result based on user selection in the web interface.

Table 'reports':

report_id (PK, int)	report_name (varchar)
1	report 1
2	report 2
3	report 3

Table 'policies':

policy_id (int)	policy_report_id (FK, int)	policy_type (int)
1	1	1
2	1	2
3	2	1
4	3	2

The user can specify (filter) if it only wants records that only contain policy type 1, type 2 or both.

Examples:

User wants reports that only have policies.policy_type of 1. Expected: reports.report_id is 2.
User wants reports that only have policies.policy_type of 2. Expected: reports.report_id is 3.
User wants reports that only have policies.policy_type of 1 and 2. Expected: reports.report_id is 1.

A simple WHERE clause obviously doesn't work as it will return reports.report_id's 1 and 2 if I would do WHERE policies.policy_type = 1 AND policies.policy_type != 2. Something along the lines of doing a WHERE clause on GROUP_CONCAT would help.

CodePudding user response：

You can use aggregation and set the conditions in the HAVING clause:

SELECT policy_report_id
FROM policies
GROUP BY policy_report_id
HAVING SUM(policy_type <> 1) = 0; -- no other than policy_type = 1

or:

HAVING SUM(policy_type <> 2) = 0; -- no other than policy_type = 2

If you want both policies 1 and 2 and no other policy:

HAVING COUNT(*) = 2 AND SUM(policy_type NOT IN (1, 2)) = 0;

The above query can be joined to reports to get the details of each report:

SELECT r.*
FROM reports r
INNER JOIN (
    SELECT policy_report_id
    FROM policies
    GROUP BY policy_report_id
    HAVING SUM(policy_type <> 1) = 0
) p ON p.policy_report_id = r.report_id

I assume that the combination of policy_report_id and policy_type in policies is unique and policy_type is not nullable.