SQL one-to-many relationship, how to select parent containing unique set of children

Question

I have a routine one-to-many schema, similar to this simple example:

PERSON
person_id (PK)

PERSON_TRAIT
person_id (FK)
trait_id (FK)
quantity

TRAIT
trait_id (PK)
name
//other attributes

Given a set of traits ("friendly, funny"), how would I return the associated person_id and quantity recordset.

At first glance I was tempted to use this but it's not this simple:

select person_id, quantity  
from trait t  
inner join person_trait pt on t.trait_id = pt.trait_id  
where name in ('friendly', 'funny')

This isn't correct because I could have a person that contains those traits plus more ("friendly, funny, skinny") and it would be returned.

To take it a step further, if there isn't a person that contains all of the traits exactly, how would I aggregate the traits from multiple different persons and return a recordset of those person_id and quantity values?

Using SQL Server 2005.

Answer 1

This will give you a list of people who have only the two traits indicated.

SELECT person_id, quantity
FROM PERSON_TRAIT
WHERE person_id IN
    (
    SELECT person_id
    FROM PERSON_TRAIT pt
    LEFT OUTER JOIN TRAIT t ON pt.trait_id = t.trait_id
        AND t.name IN ('friendly','funny')
    GROUP BY person_id
    HAVING COUNT(*) = COUNT(t.trait_id)
    )

Answer 2

Try:

select person_id, sum(quantity)
from trait  t  
inner join person_trait pt on t.trait_id = pt.trait_id 
where name in ('friendly', 'funny')
having count(distinct name) = 2

Change the having number to be the number of distinct names to be selected - so if 'friendly', 'funny' and 'clever' are required, change the having clause to be having count(distinct name) = 3.