sql question about select - trivial?

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

---

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

---

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

---

You can join the table to itself:

select t1.person
from ##temp t1
inner join ##temp t2 on t2.person = t1.person and t2.attr = '2'
where t1.attr = '1'

As this need another join for each added attribute, it's not very flexible. On the other hand, if you add distinct it will also handle duplicate attributes.

---

If I understand you correctly and you want this to be dynamic, you would either have to build your query using dynamic sql like

SELECT person FROM ###temp WHERE attr = '1' AND attr = '2'

where the WHERE part is build up using the data you provide that you would like to search for. This is however not a good idea due to the limitations it brings in form of query optimizations. So a better way would be to provide the attrs as a comma separated string and splitting it using a split function. The result of that could then be inserted into another temporary table and used in the main query with the ability to keep most of the optimizations the query analyzer could do for you.

An example of a split function could be

CREATE FUNCTION dbo.Split(@String varchar(8000), 
                          @Delimiter char(1))       
returns @temptable TABLE (items varchar(8000))       
as       
begin       
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return       
end

You could then do a query something like

SELECT person 
FROM ###temp
WHERE attr IN(SELECT CONVERT(int, items) FROM dbo.Split(@attrs, ','))
GROUP BY person 
HAVING ( COUNT(person) = @numattrs )

The @numattrs value should be the number of attributes you use in your search. Either supplied to you procedure or fetched using something like

SELECT COUNT(*) FROM dbo.Split(@attrs, ',')

Good luck!

---

I don't think doing this is going to help:

SELECT person FROM ###temp WHERE attr = '1' AND attr = '2' 

no attr value is ever going to be 1 and 2 simultaneously!!

if you make your attr field bigint, and use binary values 1,2,4,8,16 etc, then

select person from table group by person having sum (attr) = 3

will find you where person has an entry for 1 and an entry for 2 (exactly). Similarly

having sum(attr) | 11 means it includes 1,2, and 8 and maybe others. Look up bitwise functions OR, AND NOT etc NOTE this is limited by bigint, ie you can have about 63 different attr values.