Find a case which matchs with key-value pairs in SQL

Question

I have encountered a sort of problem which I couldn't find true words to explain it, imagine I have some foreign keys that have some key-value pairs:

10 => (2, 3) , (4, 5)
11 => (2, 1), (4, 5)

It can be mapped in the following table:

 ID |  FK  |   Key  |  Value
-----------------------------
 1  |  10  |    2   |    3
 2  |  10  |    4   |    5
 3  |  11  |    2   |    1
 4  |  11  |    4   |    5

I want a query to find all FKs that their pairs are (2, 3) AND (4, 5) which the answer is FK => 10 and that's where the problem appears!

If I use AND

  SELECT FK 
    FROM MyTable 
   WHERE (Key = 2 AND Value = 3) 
     AND
         (Key = 4 AND Value = 5)

It returns nothing and if I use OR:

SELECT FK 
  FROM MyTable 
 WHERE (Key = 2 AND Value = 3) 
    OR
       (Key = 4 AND Value = 5)

it returns 10 and 11 as answer which 11 is not the answer.

Actually I need more complicated query in my situation since I need to find a case that matches more than two key-value pairs.

You might want to have a look at answers in this question - https://stackoverflow.com/questions/8006901/using-tuples-in-sql-in-clause — Sudipta Mondal, Jun 25 '18 at 06:12
Check out this question and answers https://stackoverflow.com/questions/4047484/selecting-with-multiple-where-conditions-on-same-column — Abi, Jun 25 '18 at 06:25

score 2 · Answer 1 · answered Jun 25 '18 at 06:31

2

You can try INTERSECT operator:

Select FK from <<tableName>> where key = 2 and value = 3
INTERSECT
Select FK from <<tableName>> where key = 4 and value = 5

answered Jun 25 '18 at 06:31

codeLover

2,571
1
11
27

It works, I had never heard of the key word `INTERSECT`. Thanks – Muhammad Musavi Jun 25 '18 at 06:49

score 1 · Answer 2 · answered Jun 25 '18 at 06:14

Your current logic is on the right track, but anything in the WHERE clause will be applied to individual records. On the other hand, you want to place these restrictions on groups of records belonging to the same FK. If you want to find all FK having (2, 3) and (4, 5) as key value pairs, with each one occurring once, and no other pairs occurring, then use conditional aggregation with a HAVING clause:

SELECT FK
FROM yourTable
GROUP BY FK
HAVING
    COUNT(*) = 2 AND    -- only two key/value pairs
    SUM(CASE WHEN [Key] = 2 AND Value = 3 THEN 1 ELSE 0 END) = 1 AND   -- (2, 3)
    SUM(CASE WHEN [Key] = 4 AND Value = 5 THEN 1 ELSE 0 END) = 1;      -- (4, 5)

Note: You should probably avoid calling your columns (or tables, etc.) Key, because that might be a reserved keyword in SQL Server, and most flavors of SQL. Use something else.

Thanks, it works correctly, but I wish I didn't use `CASE WHEN` since it's expensive and the number of rows is going to be so much. Anyway, thanks. — Muhammad Musavi, Jun 25 '18 at 06:35

Find a case which matchs with key-value pairs in SQL

2 Answers2