Geek Answers Handbook

Check if multiple records match a set of values

Is there a way to write a single query to check if a rowset matches a row of values? I have one row for a set of values ​​that I need to map, and I would like to know if all rows match or not. I could accomplish this with a few queries, such as:

select * from tableName where (value1, value2) = ('someValue1', 'someValue2')
select * from tableName where (value1, value2) = ('someOtherValue1', 'someOtherValue2')

... etc., up to an arbitrary number of requests. How can this be rewritten as a single query when the query is returned ONLY if all values ​​match?

+5
source share
2 answers

You can try something like:

select t.* 
from tableName t
join (select 'someValue1' value1, 'someValue2' value2 union all
      select 'someOtherValue1', 'someOtherValue2') v
  on t.value1 = v.value1 and t.value2 = v.value2
where 2=
(select count(distinct concat(v1.value1, v1.value2))
 from (select 'someValue1' value1, 'someValue2' value2 union all
       select 'someOtherValue1', 'someOtherValue2') v1
 join tableName t1
   on t1.value1 = v1.value1 and t1.value2 = v1.value2)

, , .

+2

:

SELECT * 
FROM tableName 
WHERE value1 IN ('someValue1', 'someOtherValue1') AND 
      value2 IN ('someValue2', 'someOtherValue2')
+2

More articles:


All Articles