AND and OR

Question

AND and OR

Terry-380449

Default port

Points: 1458
More actions
June 22, 2007 at 8:32 am

#177194

Hello
I was wondering if there is a syntax equivalent to
field IN(value1,value2,value3)
which means field = value1 OR field = value2 OR field = value3
but for AND.
I mean instead of saying
exist table where field = value1
AND exist table where field = value2
AND exist table where field = value3
any idea?
tx!

Viewing 10 posts - 1 through 9 (of 9 total)

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

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 1

Not to my knowledge, but you would have to use different tables and fields to be meaningful anyway as WHERE TABLE.FIELD = ('A' AND 'B' AND 'C') is never going to find a result.

If you are using different tables / fields, then you could possibly use a case statement - I can't think of a good scenario where you would want to though.

David Webb-CDS SSCoach Points: 17398 More actions · Answer 2

Having not had my first cup of coffee this morning, I'm not sure I'm following this question.

Since there can't be a column with multiple values in the same row, an 'and' condition doesn't seem to be likely to ever return anything. Something like

select * from y where x = 2 and x=3

shouldn't work. It looks like your example could really be an 'or' or a union of 3 separate conditions.

And then again, I might be wrong ...
David Webb

Terry-380449 Default port Points: 1458 More actions · Answer 3

i will rephrase with and example

i need to search for all clients who have a given list of products

so i'm searching in my clients' products table, smthg like (am not sure about the syntax):

SELECT * FROM clients

WHERE EXISTS (

SELECT 1

FROM clientsproducts WHERE clients.clientid = clientsproducts.clientid AND clientsproducts.product = XXX)

AND EXISTS (

SELECT 1

FROM clientsproducts WHERE clients.clientid = clientsproducts.clientid AND clientsproducts.product = yyy)

AND EXISTS (

SELECT 1

FROM clientsproducts WHERE clients.clientid = clientsproducts.clientid AND clientsproducts.product = ZZZ)

David Webb-CDS SSCoach Points: 17398 More actions · Answer 4

How about:

SELECT *

FROM clientsproducts, clients WHERE clients.clientid = clientsproducts.clientid AND clientsproducts.product in ('xxx','yyy','zzz')

And then again, I might be wrong ...
David Webb

Terry-380449 Default port Points: 1458 More actions · Answer 5

well clientsproducts.product in ('xxx','yyy','zzz') means that the client should have any of the 3 products, while I want him to have the 3 of them, i.e I want to find 3 records, not less....

David Webb-CDS SSCoach Points: 17398 More actions · Answer 6

D'oh..

Yep, makes perfect sense. Coffee has kicked in, brain now functioning.

Your previous 'exists' query should work, I think, or use the 'in' syntax and check for a count() of 3 in the subquery.

And then again, I might be wrong ...
David Webb

David McFarland SSChampion Points: 11815 More actions · Answer 7

If this is an ad hoc need, I'm thinking a three way inner self join on derived tables (one for each of the conditions) might do the trick a bit faster than the exists, assuming that the necessary indexes are in place.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 8

There are a lot of ways to do this. You could be sneaky if you really wanted to use an IN:

SELECT * FROM Client WHERE ClientID IN (

SELECT ClientID FROM ClientsProducts

WHERE Product IN ('XXX','YYY','ZZZ')

GROUP BY ClientID

HAVING COUNT(Product) = 3)

Terry-380449 Default port Points: 1458 More actions · Answer 9

AHA! BUT...

what if i dunno the exact number of products to be tested?

i.e in may interface, the user can enter 1 to 3 products, so he might have entered just one, or filled the 3 of them...

so?