SQL syntax question (something to do with ors and ands)

voidstate

I am using php, ODBC and Filemaker Pro 5.5

Everything works fine except this query, which is making my filemaker db hang. Any idea what's wrong with it?

SELECT id, username, password, accesslevel FROM pw WHERE company='minster' AND (accesslevel=0 OR accesslevel=1 OR accesslevel=2) ORDER BY UPPER(username) ASC

Its something to do with the (accesslevel=0 OR accesslevel=1 OR accesslevel=2) bit but according to what I know of SQL I'm sure that's the right syntax for an SQL query. Or is it...

The field accesslevel is a number field so that's not the trouble.

CHeers for your help,

Ferg

Tilemachos

Try this:
SELECT id, username, password, accesslevel FROM pw WHERE (company='minster'
AND (accesslevel >= '0' AND accesslevel <= '2')) ORDER BY UPPER(username) ASC
It does not matter whether a value is numeric or not "where" command needs the values inside quotes.
Cheers> 😉

voidstate

Thanks Tilemachos,

That works, as does, I discovered, this:

SELECT id, username, password, accesslevel FROM pw WHERE company='minster' OR accesslevel BETWEEN 0 AND 2 ORDER BY UPPER(username) ASC

which is fine as long as the numbers are in series but they may not always be (the query is dynamically generated).

I have also tried this:

SELECT id, username, password, accesslevel FROM pw WHERE company='minster' AND accesslevel IN (0, 1, 2) ORDER BY UPPER(username) ASC

which doesn't work either (although I'm sure it should.)

Is there any other syntax I can try?

Any pointers would be appreciated.

Fergus