Inner Joining two tables based on all "Key/Value" Pairs matching exactly

Question

Lets say I have two tables - Person and Clothes and both of these tables have associated Key/Value tables which store attributes about a Person and an item of Clothing.

A joined version of Person to Attributes might look like:

 PersonID | AttributeKey | AttributeValue
    1          'Age'          '20'
    1          'Size'         'Large'
    2          'Age'          '20'

A joined version of Clothing to Attributes might look like:

 ClothingID | AttributeKey | AttributeValue
    99          'Age'          '20'
    99          'Color'        'Blue'
    60          'Age'          '20'

Given a specifc Piece of clothing I want to find the Person entries which match EXACTLY ALL pairs of Attributes. For example, given ClothingID 60 I want to get ONLY PersonID 2 even though PersonID 1 did have a matching AGE but it had extra attributes. And basically the opposite has the same effect.

Given Clothing 99 I would expect NO results since no Person entries have a Color attribute.

An INNER JOIN obviously gives me the Attributes of Clothing which matching specific Attributes of People. But I want to only return rows where ALL possible matches did indeed match and throw out the others if there were extra. An OUTER JOIN will give me NULL values for ones that match but how I detect this and throw out all Person rows if 1 row had NULLS?

Answer 1

Something like this:

SELECT p.*,c.* People p INNER JOIN Cloths c ON (P.key=c.key AND p.value=c.value) WHERE c.id=99

Answer 2

You can use a subquery to verify that all requirements have been met. For each combination of PersonID and ClothingID, the inner join should have a count(*) that equals the number of conditions in the person table.

Example query:

select p.PersonID, c.ClothingID
from @person p
inner join @clothing c
    on p.AttributeKey = c.AttributeKey
    and p.AttributeValue = c.AttributeValue
group by p.PersonID, c.ClothingID
having count(*) = (
    select count(*) 
    from @person p2 
    where p.PersonID = p2.PersonID
)

Output:

PersonID   ClothingID
2          60
2          99

Data used to test the query:

declare @person table (PersonID int, AttributeKey varchar(30),
    AttributeValue varchar(30))
declare @clothing table (ClothingID int, AttributeKey varchar(30),
    AttributeValue varchar(30))

insert into @person select 1, 'Age', '20'
insert into @person select 1, 'Size', 'Large'
insert into @person select 2, 'Age', '20'

insert into @clothing select 99, 'Age', '20'
insert into @clothing select 99, 'Color', 'Blue'
insert into @clothing select 60, 'Age', '20'

Answer 3

SELECT  *
FROM    persons p
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    (
                SELECT  key, value
                FROM    clothing_attributes
                WHERE   clothing_id = 99
                ) ca
        FULL JOIN
                (
                SELECT  key, value
                FROM    person_attributes
                WHERE   person_id = p.id
                ) pa
        ON      ca.key = pa.key
                AND ca.value = pa.value
        WHERE   ca.key IS NULL OR pa.key IS NULL
        )