Hi, I seem to be having trouble with WHERE... AND clauses when using the
IN predicate. In the project I'm working on it caused incorrect rows to
be returned so I tested on a small data table with a testing program that
just loops through the output of a select statement:
[Here is the table called test]
id|name
1|Apple
2|Banana
3|Cantaloupe
[Here are some test queries]
SELECT * from test WHERE name CLIKE '%a%' AND name CLIKE '%u%'
3|Cantaloupe
[which works fine]
SELECT * from test
WHERE name CLIKE '%b%' AND name IN ('Banana',
'Cantaloupe') ORDER BY name
2|Banana
3|Cantaloupe
[which should only return 'Banana']
SELECT * from test WHERE name IN ('Apple', 'Banana') AND name IN
('Banana', 'Cantaloupe')
SQL ERROR: Mismatched parentheses in WHERE clause!
[which results in an error]
SELECT * from test WHERE name IN ('Apple', 'Banana') AND id = 3
1|Apple
[which should not have returned anything]
These queries run ok for me on Sybase 11 and Access 97 (not using CLIKE
obviously). Can anyone help me, am I doing something wrong? Any response
appreciated! Thanks so much!
