Search code examples
sqlpostgresqlwhere-clause

Find rows that match all or some of the where clause conditions

If I have a table(widgets) with boolean columns like is_blue, and is_hard and some string columns like region and kind.

I can do a query like the following to get rows that match all the conditions

select * from widgets 
where is_hard = true
and is_blue = false
and region = 'NYC'
and kind = 'Deluxe';

If I also wanted to find widgets that meet 3/4 of the criteria or 2/4 and so on. Is that something I can do in SQL in one query? Or would I have to iterate in my code making less and less specific queries? I'm most familiar with Postgres and Ruby.

Solution

  • You could use below query. The sample query will return result that meet 3/4 of the criteria.

    SELECT *
FROM   widgets
WHERE  3 = (CASE WHEN is_hard = true THEN 1 ELSE 0 END)
         + (CASE WHEN is_blue = false THEN 1 ELSE 0 END)
         + (CASE WHEN region = 'NYC' THEN 1 ELSE 0 END)
         + (CASE WHEN kind = 'Deluxe' THEN 1 ELSE 0 END)