Check if multiple records match a set of values

index match return multiple values vertically
excel index match return multiple values in one cell
match two criteria and return multiple records
index match return multiple values horizontally
return all rows that match criteria excel
excel vba vlookup return multiple values
vlookup return multiple values in one cell separated by comma
index match multiple columns and rows

Is there a way to write a single query to check if a set of rows matches a set of values? I have one row per set of values that I need to match and I'd like to know if all rows are matched or not. I could perform this via multiple queries such as:

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

...and so on, up to an arbitrary number of queries. How could this sort of thing be re-written as a single query where the query returns ONLY if all values are matched?

You could 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)

If you have a large number of value pairs that you want to check, it may be easier to insert them into a temporary table and use the temporary table in the above query, instead of two separate hard-coded virtual tables.

Match two criteria and return multiple records, If I work with vlookup or Index-match I got only the first price for The image above shows you a data set in cell range B2:D19, cell value G3 lets you match values in Match two criteria and return multiple records [Excel defined Table] 1) am interested to know what is the array formula for only 1 criteria  The VLOOKUP and INDEX & MATCH formulas are great for looking up a value in a large data table and returning a result from the adjacent columns. But how can you return multiple results? What if your lookup value isn’t unique? What if it’s repeated in your data set? The standard formulas always return the first match.

What about:

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

5 easy ways to VLOOKUP and return multiple values, Picture of how to extract multiple values based on a condition using an array formula Did you know that it is also possible to VLOOKUP and return multiple values distributed over MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""),​ROWS($A$1:A1))) Vlookup with 2 or more lookup criteria and return multiple matches  To retrieve multiple matching values from a set of data with a formula, you can use the IF and SMALL functions to figure out the row number of each match and feed that value back to INDEX. In the example shown, the formula in I7 is: { = INDEX ( amts

Match if exactly two records found Select students who got q13 wrong and Q14 right

SELECT qa.StudentID FROM questionAnswer qa, Student s 
WHERE qa.StudentID=s.StudentID   AND  
      ((QuestionID=13  AND Pass=0) OR (QuestionID=14 AND Pass=1))
GROUP BY qa.StudentID
HAVING COUNT(*)=2;

The Where clause matches any records where q14 is correct and q13 is incorrect We then group by the StudentID The having requires there to be two records

Return Multiple Match Values in Excel - Xelplus, In the video below I show you 2 different methods that return multiple matches: It's quite simple to setup but it's an array formula that requires CSE. We will use the AGGREGATE function to generate a list of rows (i.e. positions) where To do this, we will test each cell in the array to see if it matches the selected Division. To retrieve multiple matching values from a set of data with a formula, you can use the IF and SMALL functions to figure out the row number of each match and feed that value back to INDEX. In the example shown, the formula in I7 is: { = INDEX ( amts

Compare two tables and find records without matches, Use the Find Unmatched Query in Access to compare two tables and identify or to add joins between the two tables (to indicate fields whose values should match​). To consolidate the tables, you must first determine which records are unique to unmatched records, but if you want to retrieve the combined set of records,  A value like 2020/21 is treated as a text value by Excel, so the text you type into C14 must match that text value. If you want someone to be able to type 2012 and have the formula return the correct result, you'll need to respecify the data table so the first column matches the potential values the users might type, i.e. 2013, 2014, 2015 etc.

FileMaker Pro 9 Bible, If more than one record in your test set included a word in the name field Only if there are multiple records with the same value in the field that is first in the sort  How to VLOOKUP multiple values in Excel with one or multiple criteria by Svetlana Cheusheva | updated on April 9, 2020 212 Comments The tutorial shows a few ways to Vlookup multiple matches in Excel based on one or more conditions and return multiple results in a column, row or single cell.

Create and run an update query - Access - Office Support, Fields in unique-values queries and unique-records queries The values in such queries are summarized. By selecting data first, you can verify that you're updating the records you On the Create tab, in the Queries group, click Query Design. Where the ProductID values in the current table match the ProductID values  Using LIKE, IN, BETWEEN, and wildcards to match multiple values in SQL Real-world data is often messy, so we need messy ways of matching values, because matching only on exact values can unintentionally filter out relevant data.

Comments
  • Hmm, but how can value1 match someValue1 and also someOtherValue1?
  • Excellent stuff - much appreciated!
  • @Mark Bannister, I don't understand the concat usage, and it seems you missed a closing parenthesis after the where. Can you explain this answer?
  • @Raffaele: I have updated the answer - I hope it's a little clearer now.
  • @MarkBannister, sorry I don't get it :( You use a count distinct on a string built from v join t instead of v1 join t1 (which is the select count scope). I don't get the point of all this code - maybe because I can't understand the original question :) See this fiddle
  • @Raffaele: mea culpa - the count distinct concat should have been on results from v1 (corrected now). See sqlfiddle.com/#!2/e81da/19 - if run with all four records in person, it returns both specified values, but if run without John's record, it returns nothing.
  • I clarified my question a bit more - in that example I am expecting two rows since I have two sets of values that I need to match. I believe the query you suggested would be satisfied by only one row since it uses IN clauses.
  • @Josh I didn't quite understand you, can you post an example?