WHERE clause with IN
-
Good afternoon everyone,
I am having trouble with the following select:
SELECT notes_key, notes_seq, notes_text, [cust-num], [contact-date], topic, contact
FROM notescustlog INNER JOIN custlog ON notes_key = [Key]
WHERE (custlog.topic IN ('Project Mg', 'Feedback', 'Serv Fail', 'Quality'))
AND (custlog.[contact-date] BETWEEN '12/01/02' AND '12/10/02')
ORDER BY topic
Both the date range and the IN list return records when run seperately, but not when combined with "AND". What am I doing wrong?
Thanks!
Jonathan
-
Maybe both predicates found no match. Did you try with OR instead if and?
-
Thanks for your reply!
I did try OR and got the expected results.
I wonder, is it possible to combine IN and AND in a single WHERE clause?
Thanks again.
-
Yes, the AND works with IN. What racosta was getting at is even though each criteria (predicate) returns records on its own, there are obviously no records that satisfy BOTH criteria. The fact that OR returns records when AND doesn't proves this.
Jay Madren
Jay Madren -
Here's a graphic explanation:
Col1 Col2
A A
A B
B A
B B
where col1 = 'A' AND col2 = 'B'
returns:
A B
That is the only row that meets the criteria. The other rows failed one or both of the criteria. For example: First row (A A): met the first part (col1 = 'A') but not the second part (col2 ='B') so it failed and was not returned.
Now:
where col1 = 'A' OR col2 = 'B'
returns:
A A
A B
B B
The first two rows are returned because they meet the first part of the OR (col1 = 'A'), the last row is returned because it meets the second part of the OR (col2 = 'B'). The row that wasn't returned did NOT meet the first criteria (col1 = 'A') since it is a B; and it did not meet the second part of the criteria (col2 = 'B') since it is an A
-SQLBill
-
Thanks all for your comments.
So that I don't seem too much the neophyte,
I should explain myself better.
My issue was with the difference between
SELECT notes_key, notes_seq, notes_text, [cust-num], [contact-date], topic, contact
FROM notescustlog INNER JOIN custlog ON notes_key = [Key]
WHERE (custlog.topic IN ('Project Mg', 'Feedback', 'Serv Fail', 'Quality'))
AND (custlog.[contact-date] BETWEEN '12/01/02' AND '12/10/02')
ORDER BY topic
Vs
SELECT notes_key, notes_seq, notes_text, [cust-num], [contact-date], topic, contact
FROM notescustlog INNER JOIN custlog ON notes_key = [Key]
WHERE (custlog.topic='ProjectMg' OR custlog.topic='Feedback' OR custlog.topic='Serv Fail' OR custlog.topic='Quality')
AND (custlog.[contact-date] BETWEEN '12/01/02' AND '12/10/02')
ORDER BY topic
The explicitly "OR'd" version was returning records while the "IN" version was not.
I have since tried both again and both return the correct records. I hate to admit this but I don't know what the problem was yesterday but I suspect a typo.
Thanks again
-
Most likely a typo. 'Project Mg' in one case and 'ProjectMg' in the other.
Don't those kind just drive you crazy?
Guarddata-
-
They do drive me crazy.
Do you find yourself leaving the code window to check the name of an object and instantly forgetting how it is written when you get back to the code? Was it a - or _ ?
Maybe its age.
-
I sometimes find that NULL values behave funny in IN statements..
maybe you have a NULL value in your 'custlog.topic' field?
This kind of thing may get you a no-resulter, as opposed to the OR statement, which tends to handle NULLs well.
- Avi
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply