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)
2
Answers
Fixed using your fiddle!
First, query
ids
wherecategory
is completed:Use this as a derived table to search
ids
matching the input list:Test it in db<>fiddle.
Alternative solution using the
bool_and()
aggregate as a window function: