AND in a WHERE clause

Question

AND in a WHERE clause

marcopolo56

SSChasing Mays

Points: 653
More actions
November 22, 2004 at 8:30 am

#66642

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 following
select 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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

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

* Noel

marcopolo56 SSChasing Mays Points: 653 More actions · Answer 2

marcopolo56

SSChasing Mays

Points: 653

November 22, 2004 at 8:53 am

#531065

Thanks Noel!

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

Another way to do this would be

WHERE Comp_id IN (1, 2)

Good Hunting!

AJ Ahrens

webmaster@kritter.net

noeld SSC Guru Points: 96590 More actions · Answer 4

Sorry but I believe that does NOT answer the question posted

* Noel

marcopolo56 SSChasing Mays Points: 653 More actions · Answer 5

Yes the second suggestion returns not only where Comp_id=1 AND Comp_id=2 but

when Comp_id=1 or Comp_id=2

But how can I accomplish a SP where I can send in a string of parameters that ALL need to be fullfilled 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 must be variable, but the criteria must be the same, all the parameters in the string have to exist to be able to return a Person_id.

The table

Person_id | Comp_id

1 1

1 2

1 3

2 2

2 3

3 1

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Well, while I somehow believe your data model has some kind of flaw, you might want to have a look at

http://www.sommarskog.se/dynamic_sql.html

http://www.sommarskog.se/arrays-in-sql.html

http://www.sommarskog.se/dyn-search.html

Should give you some ideas how you can accomplish what you're after.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 7

Try :

SELECT p1.Person_id

FROM P_Person p1

JOIN P_Person p2

ON p2.Person_id = p1.Person_id

AND p2.Comp_id = 2

WHERE p1.Comp_id = 1

shifan SSC Enthusiast Points: 153 More actions · Answer 8

You can write this query as following

select Person_id from P_Person where Comp_id>=1 and Comp_id<=2

or

select Person_id from P_Person where (Comp_id=1 or Comp_id=2)

shifan

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 9

An alternative, generic, solution would be :

SELECT Person_id

FROM P_Person

WHERE Comp_id IN (1, 2, 3, ...)

GROUP BY Person_id

HAVING COUNT(*) >= @NoParams

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 10

... and, even more generic, if your target values are in a table :

DECLARE @params Integer

SELECT @params = COUNT(*) FROM dbo.TargetValues

SELECT pp.Person_id

FROM P_Person pp

JOIN dbo.TargetValues tv

ON tv.Comp_id = pp.Comp_id

GROUP BY pp.Person_id

HAVING COUNT(pp.Comp_id) >= @NoParams

Bob Monahon Ten Centuries Points: 1282 More actions · Answer 11

I agree, both of the last two posts are better generic solutions.

Regards,

Bob Monahon