473,326 Members | 2,655 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 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 1610
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: KENNY L. CHEN | last post by:
Dear experts, I have two tables in my Oracle 8i database: TEST (COL1,COl2,REC_NO) and TEST1 (COL1,COL2,REC_NO). Both tables are unique-indexed on (COL1,COL2,REC_NO). I think the following...
6
by: Toucan | last post by:
i need to retrieve the most recent timestamped records with unique names (see working query below) what i'm having trouble with is returning the next-most-recent records (records w/ id 1 and 3...
1
by: Eric Goforth | last post by:
Hello, When I remove MYTABLE5.HAPPYCAPACITY from the SELECT clauses of the following query, the number of rows returned drops from 28 to 24. If I put it back in it goes back up to 28. Any idea...
7
by: MLH | last post by:
?dcount("","qryOwnrsDueITSwMissingAddr") when run in the immediate window return a number greater than the number of rows that display when the saved query is run - opening in the database window?...
10
by: el__marcin | last post by:
hi I am using MS office Access 2003. ANybody knows how to convert more than 300-400K rows from text into e.g. number ? I tried to do so a few times and access told me there aint enough memory or...
6
by: rshivaraman | last post by:
CREATE TABLE ( (10) NULL ) CREATE TABLE ( (10) NULL )
7
by: jb1 | last post by:
Hello All, I am trying to create a DTS package. I have two tables tbl_A and tbl_B with similar data/rows but no primary keys. tbl_A is master. I would like this package to query tbl_A and...
27
by: junky_fellow | last post by:
Guys, Can I return 0, from main() ? Is this equivalent to exit(EXIT_SUCCESS) ? thanks for any help...
101
by: Tinkertim | last post by:
Hi, I have often wondered if casting the return value of malloc() (or friends) actually helps anything, recent threads here suggest that it does not .. so I hope to find out. For instance : ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.