471,052 Members | 1,183 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,052 software developers and data experts.

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

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.
Jul 20 '05 #1
2 1513
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.


Jul 20 '05 #2
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')

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Eric Goforth | last post: by
10 posts views Thread by el__marcin | last post: by
27 posts views Thread by junky_fellow | last post: by
101 posts views Thread by Tinkertim | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.