Selecting from a Database based on a list of unique pairs

Question

For Example if I am given the following table

Id        Key             Value

 1          A             Alpha
 2          B             Alpha
 3          A             Charlie

And I took the input {(A, Charlie) and (B, Alpha)} and I asked to return all the IDs I would want it to return 2 and 3 but NOT 1.

What is the best way to do this? Can I combine it all into one query, (for speed) or would I have to run a repeat query for each value pair I received.

Answer 1

I think Postgresql has the most elegant solution:

SELECT  *
FROM    T
WHERE   ("Key", "Value") IN (('B', 'Alpha'), ('A', 'Charlie'));

SQL Fiddle Example

In SQL-SERVER 2008 and onward you can use VALUES to build your tuples:

SELECT  T.*
FROM    T
        INNER JOIN
        (   VALUES
                ('B', 'Alpha'),
                ('A', 'Charlie')
        ) v (Key, Value)
            ON v.Key = T.Key
            AND v.Value = T.Value

SQL Fiddle Example

Or for a procedure you could create a key-value pair type and pass this as a parameter:

CREATE TYPE KeyValuePair AS TABLE ([Key] VARCHAR(1), [Value] VARCHAR(7));

DECLARE @T AS KeyValuePair
INSERT @T 
VALUES
    ('B', 'Alpha'),
    ('A', 'Charlie')


SELECT  T.*
FROM    T
        INNER JOIN @T v
            ON v.[Key] = T.[Key]
            AND v.Value = T.Value;

SQL Fiddle Example

For MySQL I think you may have to just build this using AND/OR

SELECT  *
FROM    T
WHERE   (`Key` = 'A' AND `Value` = 'Charlie')
OR      (`Key` = 'B' AND `Value` = 'Alpha')

Example on SQL Fiddle

My Knowledge of other DBMS is limited, so if it is not one of the above sorry I can't be of more help.

EDIT (With the help of a_horse_with_no_name)

The PostgreSQL syntax also works for Oracle (and I think DB2)