I'm using MySQL Consider a table with columns
- id uuid
- type char
- is_active Boolean
Here are some facts about the data in this table
- type is either 'A' or 'B'
- There will be a maximum of 2 records for any id, one for each type
- 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'
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
- 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)
- 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.
Can this be achieved without using stored procedures? Any help will be greatly appreciated.