Filtering on two separate colums

Question

The schema

I'm using MySQL Consider a table with columns

1. id uuid
2. type char
3. is_active Boolean

Here are some facts about the data in this table

1. type is either 'A' or 'B'
2. There will be a maximum of 2 records for any id, one for each type
3. A record with type 'A' will exist for all IDs. A record with type 'B' is optional. That is to say, there will be a minimum of one record for any id, with type 'A'

The scenario

A user can select one or both the types to apply a filter. For example, a user might select type 'A' and filter based on is_active true. Or they might select type 'A' with is_active true AND type 'B' with is_active false.

Our task is to count the number of records which satisfies the is_active condition for selected types. Here are some scenarios better explanation

1. If user selects type 'A' and is_active true, count should include all IDs where (type is 'A' and is_active is true) or (type is 'B', regardless of is_active)
2. If user selects type 'A' with is_active true and type 'B' with is_active false, count should include all IDs where (type is 'A' and is_active is true) or (type is 'B' and is_active is false)

If user restricts is_active for type 'B', IDs without type 'B' should not be included.

If user restricts is_active for type 'A', IDs without type 'B' should be included.

The question

Can this be achieved without using stored procedures? Any help will be greatly appreciated.

Answer 1

As an example

SELECT a.*, b.*
-- SELECT COUNT(*)
FROM      datatable a
LEFT JOIN datatable b ON  a.id   = b.id 
                      AND b.type = 'B'
WHERE a.type = 'A'
  AND (CASE @filterA
       WHEN 'A active'     THEN a.is_active 
       WHEN 'A not active' THEN NOT a.is_active
       ELSE 1              -- A is any
       END)
  AND (CASE @filterB
       WHEN 'B active'     THEN b.is_active 
       WHEN 'B not active' THEN NOT b.is_active
       WHEN 'B absent'     THEN b.id IS NULL
       ELSE 1              -- B is any
       END)

@filterA and @filterB are variables/literals with user filters choices inserted/transferred into the query as a parameter or literally (edit WHEN conditional values for to match).