| ID | name | isSearchable |
|---|---|---|
| 1 | foo | true |
| 2 | bar | true |
| 3 | zar | false |
I’ve got some ids and I need to filter records where they have isSearchable = true.
This query give as result ID = 1 because is searchable, but I would to apply the filter isSearchable to the entire result, not row-by-row.
SELECT *
FROM my_table
WHERE id IN (1, 3)
AND isSearchable = true
So in this case I’m expecting no-results because both records should be in first isSearchable and after that, filter the ids.
I’ve tried experimenting with sub-query etc but the in operator (or the or operator) but I’m not able to accomplish the result.
Maybe is something really simple, but I’ve no ideas on how to solve.
Thanks for your help.
>Solution :
One approach using a window function:
SELECT ID
FROM (SELECT ID,
MIN(isSearchable::INT) OVER() AS minSearchable
FROM my_table
WHERE id IN (1,3)) cte
WHERE minSearchable = 1
Check the demo here.