CODEWITHSUNDEEP
sqlsql-server
David Wright
David Wright

Reputation: 805

Matching multiple key/value pairs in SQL

I have metadata stored in a key/value table in SQL Server. (I know key/value is bad, but this is free-form metadata supplied by users, so I can't turn the keys into columns.) Users need to be able to give me an arbitrary set of key/value pairs and have me return all DB objects that match all of those criteria.

For example:

Metadata:
Id Key Value
1  a   p
1  b   q
1  c   r
2  a   p
2  b   p
3  c   r

If the user says a=p and b=q, I should return object 1. (Not object 2, even though it also has a=p, because it has b=p.)

The metadata to match is in a table-valued sproc parameter with a simple key/value schema. The closest I have got is:

select * from [Objects] as o
where not exists (
  select * from [Metadata] as m
  join @data as n on (n.[Key] = m.[Key])
  and n.[Value] != m.[Value]
  and m.[Id] = o.[Id]
)

My "no rows exist that don't match" is an attempt to implement "all rows match" by forming its contrapositive. This does eliminate objects with mismatching metadata, but it also returns objects with no metadata at all, so no good.

Can anyone point me in the right direction? (Bonus points for performance as well as correctness.)

Upvotes: 2

Views: 6349

Answers (2)

Squirrel
Squirrel

Reputation: 24763

; WITH Metadata (Id, [Key], Value) AS  -- Create sample data
(
    SELECT 1,  'a',   'p' UNION ALL
    SELECT 1,  'b',   'q' UNION ALL
    SELECT 1,  'c',   'r' UNION ALL
    SELECT 2,  'a',   'p' UNION ALL
    SELECT 2,  'b',   'p' UNION ALL
    SELECT 3,  'c',   'r' 
),
data ([Key], Value) AS  -- sample input
(
    SELECT 'a', 'p' UNION ALL
    SELECT 'b', 'q'
),
-- here onwards is the actual query
data2 AS
(
    -- cnt is to count no of input rows
    SELECT [Key], Value, cnt = COUNT(*) OVER() 
    FROM data
)
SELECT m.Id
FROM Metadata m
INNER JOIN data2 d ON m.[Key] = d.[Key] AND m.Value= d.Value
GROUP BY m.Id
HAVING COUNT(*) = MAX(d.cnt)

Upvotes: 1

Ɖiamond ǤeezeƦ
Ɖiamond ǤeezeƦ

Reputation: 3331

The following SQL query produces the result that you require.

SELECT *
FROM @Objects m
WHERE Id IN
(
    -- Include objects that match the conditions:
    SELECT m.Id
    FROM @Metadata m
    JOIN @data d ON m.[Key] = d.[Key] AND m.Value = d.Value

    -- And discount those where there is other metadata not matching the conditions:
    EXCEPT
    SELECT m.Id
    FROM @Metadata m
    JOIN @data d ON m.[Key] = d.[Key] AND m.Value <> d.Value
)

Test schema and data I used:

-- Schema
DECLARE @Objects TABLE (Id int);
DECLARE @Metadata TABLE (Id int, [Key] char(1), Value char(2));
DECLARE @data TABLE ([Key] char(1), Value char(1));

-- Data
INSERT INTO @Metadata VALUES
  (1,  'a',   'p'),
  (1,  'b',   'q'),
  (1,  'c',   'r'),
  (2,  'a',   'p'),
  (2,  'b',   'p'),
  (3,  'c',   'r');

INSERT INTO @Objects VALUES
  (1),
  (2),
  (3),
  (4);  -- Object with no metadata

INSERT INTO @data VALUES
  ('a','p'),
  ('b','q');

Upvotes: 0

Related Questions