Return rows which satisfy BOTH of my WHERE CLAUSES, not EITHER???

[Alan Mailer]

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.

[Alan Mailer]

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.MyTabl e2Field, MyTable3.MyTabl e3Field
FROM (MyTable1
LEFT JOIN MyTable3 ON MyTable1.TableI DField = MyTable3.TableI DField)
LEFT JOIN MyTable2 ON MyTable1.id_MyT able2Field =
MyTable2.id_MyT able2Field
WHERE (MyTable2.MyTab le2Field='X')
AND (MyTable3.MyTab le3Field LIKE 'A%')

.... The (unwanted) result of this statement is a set of rows from
MyTable1 which have 'X' in the MyTable2.MyTabl e2Field column **OR**
begin with 'A' in the MyTable3.MyTabl e3Field 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.MyTabl e3Field column **AND** have 'X' as the value in the
MyTable2.MyTabl e2Field 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**********@e arthlink.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.

[Reply-to-group Thx]

Alan Mailer 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.

Try:

SELECT * FROM MyTable WHERE (Field1 Like 'A%' And Field2 = 'X')