Newbie question - my AND query doesn't work

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

SQL Server 2005 Forums

Transact-SQL (2005)

Newbie question - my AND query doesn't work

Author

Topic

dmoonme
Starting Member

13 Posts

Posted - 2009-06-02 : 17:05:43

Database table contains this data

OBJECTID|SURVEYID|FIELDID|VALUE1|VALUE2
1,100,320,2,3
2,100,321,2,2
3,101,320,2,4
4,101,321,1,1

Here's my query but nothing is returned.

SELECT * FROM SurveyDetails
WHERE (([FIELDID] = 320) AND (([VALUE1]) >= 2) AND (([VALUE2]) >= 0)) AND (([FIELDID] = 321) AND (([VALUE1]) >= 2) AND (([VALUE2]) >= 0))

I want to build a query to get records where FIELDID=320 AND VALUE1 >= 2 AND FIELDID=321 AND VALUE1 >=2. How do I do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-02 : 17:16:46

SELECT * FROM SurveyDetails
WHERE Value1 >= 2 AND FieldID IN (320, 321)

E 12°55'05.63"
N 56°04'39.26"

dmoonme
Starting Member

13 Posts

Posted - 2009-06-02 : 17:34:06

How about if I want to get records with different values of VALUE1 AND VALUE2 for each FIELDID?

For example,

(FIELDID=320 AND VALUE1 >=2 AND VALUE2 >=3)
AND
(FIELDID=321 AND VALUE1 >=1 AND VALUE2 >=2)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-02 : 17:37:31

(FIELDID=320 AND VALUE1 >=2 AND VALUE2 >=3)
OR
(FIELDID=321 AND VALUE1 >=1 AND VALUE2 >=2)

E 12°55'05.63"
N 56°04'39.26"

dmoonme
Starting Member

13 Posts

Posted - 2009-06-02 : 19:31:15

Thanks Peso. Your last suggestion works with the OR operator. Out of curiousity why doesn't the AND operator work?

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-02 : 22:33:18