1

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.

  • What database? Oracle? SQL Server? homework question? – Mark Stewart Oct 9 at 23:15
  • 1
    Database is MySQL. And no, this isn't homework.😅 – Hari Krishnan Oct 10 at 4:30
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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.