sql question about select - trivial?

Question

seems trivial, but can't find solution -

i need to write query which gets me persons based on values of attributes (e.g. get me persons which have attr '1' AND '2' AND '3')

*clarification: querying could be done on more than three value of attributes - it will be user selected - from 0 to n values, but i don't expect more than 7 values... *

-- tsql script --------------
create table ##temp (person char(1), attr char(1)  );

-- can be 1..n persons and 1..n attributes
insert into ##temp VALUES 
('A','1'),
('A','2'),
('B','1'),
('C','2');

-- sample: get all persons which have attribute 1 AND 2
-- sample: result should be 'A' only

drop table ##temp
-- tsql script -----------------

thanks for helping, hh

Answer 1

You could use a GROUP BYwith a HAVING COUNT(DISTINCT)clause.

SQL Statement

SELECT  person
FROM    ##temp
WHERE   attr IN ('1', '2')
GROUP BY 
        person
HAVING  COUNT(DISTINCT attr) = 2

Following statement will always outperform the COUNT(DISTINCT) but will yield incorrect results if duplicates are present. please note that the outperformance might not be measurable.

SELECT  person
FROM    ##temp
WHERE   attr IN ('1', '2')
GROUP BY 
        person
HAVING  COUNT(*) = 2

Answer 2

normal you have 1 table for person where every person is listed singletime, and a second table with the atributes

select person from (
  select person, count(person) as cnt from ##temp where attr in (1,2,3) group by person
) where cnt = 3

this should do the work

but in your testing table was only attr 1 and 2 ... so this won't show any results

select person from (
  select person, count(person) as cnt from ##temp where attr in (1,2) group by person
) where cnt = 2

will show you A