Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Condition to filter records with "in" and "and" operators

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading