Mulitiple and statement - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Mulitiple and statement

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-08-17 : 04:49:36

hi

I run the query:
select * into tbldemo from tblDEMOA
where AC >= '10' and AC <= '200' and AC = 'Z100'

and return 0 rows affected so i changed to

select * into tbldemo from tblDEMOA
where AC >= '10' and AC <= '200' or AC = 'Z100'

and it is fine. I am just wondering why AND doesn't work?

Thanks

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-17 : 05:19:39

Well, And does work in the first query, you are trying to get the records where AC is
between 10 to 200 and also it should be Z100 which conflicted with the first condition so it didint return any records.

Where as in the second statment you are using or opertors that means that fetch all the records where AC is between 10 to 200 or AC equal to Z100.

What you are trying to do??

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 05:31:28

AND, as operator, has higher priority than OR operator.

The first comparison, where AC >= '10' and AC <= '200' and AC = 'Z100', searches for all AC values that is greater than (or equal to) '10' and less than (or equal to) '200'. And as addition to this, you also want AC value to be 'Z100'. This is not possible. Sorting them alphabetically gives '10', '200', 'Z100'. This means 'Z100' is out of range anyway.

Second comparison, AC >= '10' and AC <= '200' or AC = 'Z100', searches the same range ('10' <= AC <= '200'). In addition to this AC can be (OR) also 'Z100'. This is ok.

Ans since you have AC >= '10' and AC <= '200' or AC = 'Z100', the AC >= '10' and AC <= '200' part is evaluated separately from the OR comparison.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 05:36:46

See this simpified comparison, WHERE A = 10 AND B = 20 OR C = 30.
This will only evaluate to TRUE, if A = 10 AND B = 20, or any any case as long as C = 30.

See this simpified comparison, WHERE A = 10 AND (B = 20 OR C = 30).
This will only evaluate to TRUE, if A = 10 together with any other case where B = 20 or C = 30.

Peter Larsson
Helsingborg, Sweden

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-08-18 : 04:17:10

hi all

Actually i want to include records which are >= 10 and <= 200 and also include Z100 only as well. Thanks guys

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-19 : 02:08:25

quote:
Originally posted by sg2255551

hi all

Actually i want to include records which are >= 10 and <= 200 and also include Z100 only as well. Thanks guys

Then in that guess OR operator should work for you.

if you want to include all the records which are between 10 and 200 and also the record which is Z100, then OR operator should be fine.

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources