Query record categories if a no record has missing data in that category

Question

Let's say I have the following data table:

id | data_1 | data_2 | category
1  |    a   |    A   |    1    
2  |    a   |    A   |    1    
3  |        |    B   |    2    
4  |    b   |    B   |    2    
5  |    b   |    B   |    2    
6  |    c   |        |    3    
7  |    c   |        |    3    
8  |    c   |    C   |    3    
9  |        |    D   |    4 
10 |    d   |        |    4    
11 |    d   |    D   |    4    
12 |    d   |    D   |    4    
13 |    e   |    E   |    5    
14 |    e   |    E   |    5     
15 |    f   |    F   |    6      

and a list of (record) id as input:

1, 5, 12, 13, 14

I'd like to make a query which returns all those categories which are "referenced" by any of the record in the input list and the referenced category is "complete".

By

• referenced category I mean that at least one record from the input list belongs to that category
• complete category I mean that all record that belongs to that category has value (i.e. not NULL or empty string) in both data_1 and data_2 column. (So in the example table category 1, 5 and 6 is complete)

So for the example table and input the result would be:

category
   1    
   5    

(And for an input of 4, 5, 6 the result would be NULL or empty list)

Is it possible to make single query for this?

(fiddle)

Answer 1

First, query ids where category is completed:

select
    category,
    array_agg(id) as ids
from tbl
group by category
having bool_and(nullif(data_1, '') || nullif(data_2, '') is not null) 
order by category;

 category |   ids
----------+---------
        1 | {1,2}
        5 | {13,14}
        6 | {15}
(3 rows)

Use this as a derived table to search ids matching the input list:

select category
from (
    select
        category,
        array_agg(id) as ids
    from tbl
    group by category
    having bool_and(nullif(data_1, '') || nullif(data_2, '') is not null) 
    ) s
where ids && array[1, 5, 12, 13, 14] -- input parameter
order by category;

 category
----------
        1
        5
(2 rows)

Test it in db<>fiddle.

Alternative solution using the bool_and() aggregate as a window function:

select distinct category
from (
    select 
        id, 
        category,
        bool_and(nullif(data_1, '') || nullif(data_2, '') is not null) over w as completed
    from tbl
    window w as (partition by category)
    ) s
where completed
and id = any(array[1, 5, 12, 13, 14]) -- input parameter
order by category;