Multiple AND conditions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple AND conditions

Author

Topic

smckechnie
Starting Member

7 Posts

Posted - 2006-09-12 : 01:26:35

Hi All.

Is there a way to have multiple AND conditions on the same field in a database.

EXAMPLE

SELECT * FROM tbl
WHERE field1 = 1 AND field1 = 2 AND field1 = 5

Thanks

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-09-12 : 01:34:05

Not the way you've done it. I'm assuming you're trying to find out if the given field contains all the characters 1, 2 & 5?

Try using the LIKE function for this.

SELECT * from tbl
WHERE field1 LIKE '%1%' AND field1 LIKE '%2%' etc etc

smckechnie
Starting Member

7 Posts

Posted - 2006-09-12 : 01:46:13

Hi Timmy thanks for the quick response, however what I am trying to do is. I have a table with contact details like 1 = Home, 2 = work, 3 = Fax, 4 = email, 5 = Cell. Now these are captured into a table with the clientID number on individual rows.

Now I am trying to retreave clients that have Home,work and Cell contact number stored in the tables.

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-12 : 01:50:08

Try ORing the conditions:

Select * from Tbl
Where Field1 = 1 or Field1 = 2 or Field1 = 3 or Field1 = 4 or Field1 = 5

Another solution is to use IN clause:

Select * from Tbl
where Field1 in (1,2,3,4,5)

Harsh Athalye
India.
"Nothing is Impossible"

smckechnie
Starting Member

7 Posts

Posted - 2006-09-12 : 01:52:24

Thanks Harsh.

But using IN and OR will return clients with any one of those values I am looking for clients with all the values

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-12 : 02:15:16

Select ClientID
from Tbl
Group by ClientID
Having Count(ContactDetails) = 5

Of course, I am assuming here that each clientID can't have any of the contact detail type (1 = home, 2 = work etc.) repeated !

Harsh Athalye
India.
"Nothing is Impossible"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-12 : 02:20:35

How are you saving the ClientId in the column field1? is it Comma Seperated?

Chirag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-12 : 02:22:33

add a distinct if you have duplicate contact detail type


 Select ClientID
from Tbl
Group by ClientID
Having Count(distinct ContactDetails) = 5

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-12 : 02:44:50

Another solution can be this, although costly performance-wise:

SELECT DISTINCT X.ClientID FROM TBL X
JOIN
(SELECT ClientID FROM TBL WHERE ContactDetails  = 1) AS X1
ON X.ClientID  = X1.ClientID
JOIN
(SELECT ClientID FROM TBL WHERE ContactDetails  = 2) AS X2
ON X.ClientID  = X2.ClientID
JOIN
(SELECT ClientID FROM TBL WHERE ContactDetails  = 3) AS X3
ON X.ClientID  = X3.ClientID
JOIN
(SELECT ClientID FROM TBL WHERE ContactDetails  = 4) AS X4
ON X.ClientID  = X4.ClientID
JOIN
(SELECT ClientID FROM TBL WHERE ContactDetails  = 5) AS X5
ON X.ClientID  = X5.ClientID

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-13 : 09:31:07

Select ClientID
from Tbl
Where contactDetails in(1,2,5)
Group by ClientID
Having Count(distinct ContactDetails) = 3

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources