SQL Server Performance Forum – Threads Archive
AND in a WHERE clause
I have a 2 column tabel, with column name Person_id and Comp_id I would like to select the Person_id(s) that satisfy criterias like the followingselect Person_id from P_Person where Comp_id=1 and Comp_id=2 But this does not return anything, so how do I accomplish a select statment that will return those Person_id(s) that fullfill my critera and match both comp_id=1 and comp_id=2 ? so specific person_id(s) will be present several times in Personid column
Use OR instead of AND. Another way to do it is SELECT Person_id FROM P_Person WHERE Comp_id IN (1,2) which amounts to the same thing. If you only want 1 person_id to be returned for this, use SELECT DISTINCT Person_id …. Tom Pullen
DBA, Oxfam GB
But or is not OK, beacuse I need both 1 AND 2 to exist. not either of them, but only return those Person_id that have both 1 and 2 OK Got the answer from an other site select P1.Person_id from P_Person P1 join P_Person P2 on P1.Person_id =P2.Person_ID where P1.Comp_id=1 and P2.Comp_id=2
But how can I accomplish a SP where I can send in a string of parameters that ALL need to exist (Comp_id) in the way as the example above? IS this possible without knowing how many parameters to send in to the SP? Ex of the string with comp_id. (1,2,3) or (1) or (1,2) : so the string should be variable, but the criteria must be that, all the parameters in the string have to exixt to be able to return a Person_id
Hi ya, you’d probably need to pass in the variables in a delimited string, then convert that string into a temp table or table variable then you can combine the two
select @param_count = count(*)
from @my_params select p.person_id
from @my_params mp
inner join P_person p
on p.comp_id = mp.comp_id
group by
p.person_id
having
count(*) = @param_count
Cheers
Twan
]]>