My apologies. My previous question on this subject was misleading I
think. I tried to simplify the example in my post and did it in such
a way that the problem I'm actually having wasn't expressed.
The actual situation is this: I'm not just performing a WHERE against
a single table. I'm doing it at the end of a 'join'-oriented
statement:
SELECT MyTable1.*, MyTable2.MyTable2Field, MyTable3.MyTable3Field
FROM (MyTable1
LEFT JOIN MyTable3 ON MyTable1.TableIDField = MyTable3.TableIDField)
LEFT JOIN MyTable2 ON MyTable1.id_MyTable2Field =
MyTable2.id_MyTable2Field
WHERE (MyTable2.MyTable2Field='X')
AND (MyTable3.MyTable3Field LIKE 'A%')
.... The (unwanted) result of this statement is a set of rows from
MyTable1 which have 'X' in the MyTable2.MyTable2Field column **OR**
begin with 'A' in the MyTable3.MyTable3Field field.
Again, what I'm after is a result set which will consist only of rows
which satisfy **ALL** of my where conditions. Specifically, every row
in the result set should begin with the letter 'A' in the
MyTable3.MyTable3Field column **AND** have 'X' as the value in the
MyTable2.MyTable2Field column.
I apologize for the over-simplified form of the earlier message. But,
I still need help! Thanks.
On Thu, 20 May 2004 16:32:32 GMT, Alan Mailer
<cl**********@earthlink.net> wrote:
Imagine I had a table called MyTable with the following values:
Field1 Field2
____ _____
A X
AA R
B X
BB T
Now, say I want to return only rows which match *BOTH* of the
following criteria:
Criteria 1: Field1 Like A%
Criteria 2: Field2 = 'X'
The sql I've written is not giving me the specific results I want:
SELECT * FROM MyTable WHERE Field1 Like 'A%' And Field2 = 'X'
returns:
Field1 Field2
____ _____
A X
AA R
B X
...Now, I understand how this fufills the sql statement I'm writing,
but that statement must not be expressing what I really want. What I
really want is a result set consisting only of rows which match BOTH
of my criteria; not *either* of my criteria. If I wrote the sql
statement correctly, the result set would/should be this:
Field1 Field2
____ _____
A X
...You see the difference I'm sure. The above represents the only
record in MyTable in which Field1 starts with the letter "A"
***AND*** Field2 is an "X".
I know this is a very very basic question to ask, but could someone
help me with the sql statement I'm looking for? Thanks in advance.