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

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
        )