I am having trouble querying a SQL Anywhere Database to select one column only if all conditions are met. Here is an example:

SELECT USER FROM TABLE1
 WHERE DATA0 < 1
   AND DATA1 = 'F'
   AND ID = 23
   AND ID = 232
   AND CITY = 'CITY1'
    OR CITY = 'CITY2'
    OR CITY = 'CITY3'

So I want USER to be returned only if all the conditions are true

asked 21 Aug '13, 22:46

t2nator's gravatar image

t2nator
59225
accept rate: 0%

edited 22 Aug '13, 02:27

Mark%20Culp's gravatar image

Mark Culp
22.9k9129269


Because AND is above OR in SQL Anywhere's operator precedence you need to place your last three conditions in parenthesis:

SELECT USER FROM TABLE1
 WHERE DATA0 < 1
  AND DATA1 = 'F'
  AND ID = 23
  AND ID = 232
  AND (CITY = 'CITY1' OR CITY = 'CITY2' OR CITY = 'CITY3')

Otherwise you will get every row that satisfies one of the OR'ed CITY conditions.

EDIT: As crb pointed out, this query will not likely return the desired result because you are requiring ID to equal both 23 and 232. These should also be in a OR/IN statement.

permanent link

answered 22 Aug '13, 00:20

Mikel%20Rychliski's gravatar image

Mikel Rychliski
2.1k1641
accept rate: 30%

edited 22 Aug '13, 15:21

And if you want ID to be OR'ed as well, using the IN operator, you can use WHERE ID IN (23, 232) AND CITY IN ('CITY1', 'CITY2', 'CITY3')

permanent link

answered 22 Aug '13, 15:06

crb's gravatar image

crb
1263310
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×12

question asked: 21 Aug '13, 22:46

question was seen: 764 times

last updated: 22 Aug '13, 15:21