Inner Joining two tables based on all "Key/Value" Pairs matching exactly
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 开发者_Python百科had NULLS?
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
)
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'
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
精彩评论