Multiple AND statements

[GAA]

I have written a Custom Query using the Database Results Wizard as follows:

SELECT * FROM Results WHERE (Name = '::SelectName::' AND Activity =
'::SelectActivity::') OR (Name = '::SelectName::' AND Purpose =
'::SelectPurpose::') OR (Name = '::SelectName::' AND Activity =
'::SelectActivity::' AND Purpose = '::SelectPurpose::')

The first two parts of this work fine and return the correct results if only
two criteria option are selected, however when all three criteria selection
are selected the the results returned are incorrect, it seems to only
recognise and match the Name part.

Any ideas?

 

[Stefan B Rusynko]

Logic error
- make your 3rd condition the 1st condition

In any select with OR the 1st criteria met will yield the results
(even if any further tests could also yield results)
And the results are determined from left to right
(so the 1st test that fails, yields an F, determines the test result)
- in your case test 1 and test 2 already have determined the results of test 3 before it is even seen
Look at your code as simple Boolean tests
- below is annotated w/ simple results as XtestT/F meaning results of comparison are True or False

SELECT * FROM Results
WHERE
(Name='::SelectName::' AND Activity='::SelectActivity::')
(NtestT/F and AtestT/F) which yields T OR F (test1) - say T
OR
(Name='::SelectName::' AND Purpose='::SelectPurpose::')
(NtestT/F and PtestT/F) which yields T OR F (test2) - say T
OR
(Name='::SelectName::' AND Activity='::SelectActivity::' AND Purpose='::SelectPurpose::')
(NtestT/F and AtestT/F and PtestT/F)
is the same as ( test1T/F and PtestT/F ) which yields T OR F (test 3)
- and by this time either test1 or test2 has yielded T so they would have been selected for results
(or if test1 has yielded F, test 3 will always yield F)

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
_____________________________________________


|I have written a Custom Query using the Database Results Wizard as follows:
|
| SELECT * FROM Results WHERE (Name = '::SelectName::' AND Activity =
| '::SelectActivity::') OR (Name = '::SelectName::' AND Purpose =
| '::SelectPurpose::') OR (Name = '::SelectName::' AND Activity =
| '::SelectActivity::' AND Purpose = '::SelectPurpose::')
|
| The first two parts of this work fine and return the correct results if only
| two criteria option are selected, however when all three criteria selection
| are selected the the results returned are incorrect, it seems to only
| recognise and match the Name part.
|
| Any ideas?

 

[GAA]

Thanks Stefan,

However I'm obviously still doing something wrong as I still can't get it to
work. Can you re-write my original query for me, so I know how it should read?

Thanks

 

[Stefan B Rusynko]

Rewriting won't correct logic assumption errors
(that your data will match the query)
But here it is
(all lines below are on 1 line!)

SELECT * FROM Results WHERE
(Name='::SelectName::' AND Activity='::SelectActivity::' AND Purpose='::SelectPurpose::')
OR (Name='::SelectName::' AND Activity='::SelectActivity::')
OR (Name='::SelectName::' AND Purpose='::SelectPurpose::')

Test each condition separately
For the 1st one just use

SELECT * FROM Results WHERE
(Name='::SelectName::' AND Activity='::SelectActivity::' AND Purpose='::SelectPurpose::')

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
_____________________________________________


| Thanks Stefan,
|
| However I'm obviously still doing something wrong as I still can't get it to
| work. Can you re-write my original query for me, so I know how it should read?
|
| Thanks
|
| "Stefan B Rusynko" wrote:
|
| > Logic error
| > - make your 3rd condition the 1st condition
| >
| > In any select with OR the 1st criteria met will yield the results
| > (even if any further tests could also yield results)
| > And the results are determined from left to right
| > (so the 1st test that fails, yields an F, determines the test result)
| > - in your case test 1 and test 2 already have determined the results of test 3 before it is even seen
| > Look at your code as simple Boolean tests
| > - below is annotated w/ simple results as XtestT/F meaning results of comparison are True or False
| >
| > SELECT * FROM Results
| > WHERE
| > (Name='::SelectName::' AND Activity='::SelectActivity::')
| > (NtestT/F and AtestT/F) which yields T OR F (test1) - say T
| > OR
| > (Name='::SelectName::' AND Purpose='::SelectPurpose::')
| > (NtestT/F and PtestT/F) which yields T OR F (test2) - say T
| > OR
| > (Name='::SelectName::' AND Activity='::SelectActivity::' AND Purpose='::SelectPurpose::')
| > (NtestT/F and AtestT/F and PtestT/F)
| > is the same as ( test1T/F and PtestT/F ) which yields T OR F (test 3)
| > - and by this time either test1 or test2 has yielded T so they would have been selected for results
| > (or if test1 has yielded F, test 3 will always yield F)
| >
| > --
| >
| > _____________________________________________
| > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > "Warning - Using the F1 Key will not break anything!" (-;
| > To find the best Newsgroup for FrontPage support see:
| > http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
| > _____________________________________________
| >
| >
| > |I have written a Custom Query using the Database Results Wizard as follows:
| > |
| > | SELECT * FROM Results WHERE (Name = '::SelectName::' AND Activity =
| > | '::SelectActivity::') OR (Name = '::SelectName::' AND Purpose =
| > | '::SelectPurpose::') OR (Name = '::SelectName::' AND Activity =
| > | '::SelectActivity::' AND Purpose = '::SelectPurpose::')
| > |
| > | The first two parts of this work fine and return the correct results if only
| > | two criteria option are selected, however when all three criteria selection
| > | are selected the the results returned are incorrect, it seems to only
| > | recognise and match the Name part.
| > |
| > | Any ideas?
| >
| >
| >

 

[GAA]

This seems so obvious, I'm obviously doing something silly!

Each condition works separately, and conditions 2 and 3 work fine when
condition 1 isn't included. However as soon as I introduce condition 1,
whether it be at the beginning, the middle or the end, it simply does not
return what I expect!

The SelectName, SelectActivity and SelectPurpose variables are selected by
the user from drop-down lists. If only two conditions are used i.e. user only
selects criteria from 2 of the 3 drop-down lists then the correct results are
returned. When the user selects criteria from all 3 drop-down lists, it only
returns results for one of the variables being matched i.e. SelectName.

For example:
Say I select 'Greg Aitken' from the SelectName drop-down list, 'Meeting'
from the SelectActivity drop-down list and 'Teachers' from the SelectPurpose
drop-down list it returns all the records which have 'Greg Aitken' in the
Name field and ignores the other two conditions, even though there definitely
are records that match all 3 criteria.

What am I doing wrong? I'm beginning to think it's not my query but
something else?

Thanks again
Greg

Rewriting won't correct logic assumption errors
(that your data will match the query)
But here it is
(all lines below are on 1 line!)

SELECT * FROM Results WHERE
(Name='::SelectName::' AND Activity='::SelectActivity::' AND Purpose='::SelectPurpose::')
OR (Name='::SelectName::' AND Activity='::SelectActivity::')
OR (Name='::SelectName::' AND Purpose='::SelectPurpose::')

Test each condition separately
For the 1st one just use

SELECT * FROM Results WHERE
(Name='::SelectName::' AND Activity='::SelectActivity::' AND Purpose='::SelectPurpose::')

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
_____________________________________________


| Thanks Stefan,
|
| However I'm obviously still doing something wrong as I still can't get it to
| work. Can you re-write my original query for me, so I know how it should read?
|
| Thanks
|
| "Stefan B Rusynko" wrote:
|
| > Logic error
| > - make your 3rd condition the 1st condition
| >
| > In any select with OR the 1st criteria met will yield the results
| > (even if any further tests could also yield results)
| > And the results are determined from left to right
| > (so the 1st test that fails, yields an F, determines the test result)
| > - in your case test 1 and test 2 already have determined the results of test 3 before it is even seen
| > Look at your code as simple Boolean tests
| > - below is annotated w/ simple results as XtestT/F meaning results of comparison are True or False
| >
| > SELECT * FROM Results
| > WHERE
| > (Name='::SelectName::' AND Activity='::SelectActivity::')
| > (NtestT/F and AtestT/F) which yields T OR F (test1) - say T
| > OR
| > (Name='::SelectName::' AND Purpose='::SelectPurpose::')
| > (NtestT/F and PtestT/F) which yields T OR F (test2) - say T
| > OR
| > (Name='::SelectName::' AND Activity='::SelectActivity::' AND Purpose='::SelectPurpose::')
| > (NtestT/F and AtestT/F and PtestT/F)
| > is the same as ( test1T/F and PtestT/F ) which yields T OR F (test 3)
| > - and by this time either test1 or test2 has yielded T so they would have been selected for results
| > (or if test1 has yielded F, test 3 will always yield F)
| >
| > --
| >
| > _____________________________________________
| > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > "Warning - Using the F1 Key will not break anything!" (-;
| > To find the best Newsgroup for FrontPage support see:
| > http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
| > _____________________________________________
| >
| >
| > |I have written a Custom Query using the Database Results Wizard as follows:
| > |
| > | SELECT * FROM Results WHERE (Name = '::SelectName::' AND Activity =
| > | '::SelectActivity::') OR (Name = '::SelectName::' AND Purpose =
| > | '::SelectPurpose::') OR (Name = '::SelectName::' AND Activity =
| > | '::SelectActivity::' AND Purpose = '::SelectPurpose::')
| > |
| > | The first two parts of this work fine and return the correct results if only
| > | two criteria option are selected, however when all three criteria selection
| > | are selected the the results returned are incorrect, it seems to only
| > | recognise and match the Name part.
| > |
| > | Any ideas?
| >
| >
| >

 

[Kevin Spencer]

I'm nost sure what you're expecting, but I can help you understand why you're getting the results you're getting, and then you should be able to figure it out for yourself.

Let's talk a bit about logic, which is what boolean (and/or) statements are all about:

I'm going to substitute some things in the query to make it clearer.

This is what is going to happen if the following resolves to true:

SELECT * FROM Results

"WHERE" indicates a test. The WHERE clause must resolve to true.
WHERE

Now I will do some translation:

(Name='::SelectName::' AND Activity='::SelectActivity::' AND Purpose='::SelectPurpose::')

AND indicates that ALL must resolve to true. Therefore, the following can be said to be a translation of this condition:

A is true AND B is true AND C is true

The same for the next:

(Name='::SelectName::' AND Activity='::SelectActivity::')

A is true AND B is true

And the last:

(Name='::SelectName::' AND Purpose='::SelectPurpose::')

A is true AND C is true

Now for the tricky part. "OR" indicates that EITHER statement can resolve to true. So, here's a picture of the simplified condition:

(A is true AND B is true AND C is true)
- OR-
(A is true AND B is true)
- OR-
(A is true AND C is true)


But let's simply the equation, by referring to the 3 statements as Statement1, Statement2, and Statement3. Now we have the following:

Statement1 is true OR Statement2 is true OR Statement3 is true

In other words, ANY of these statements being true will cause the overall condition to return true.

Now, what do all 3 of these statements have in common?

A is true

Therefore, A MUST BE TRUE for the whole thing to return true. If A is false, the whole thing is false, because A is ANDed into each of the 3 Statements.

What else do the 3 statments have in common?

Nothing.

Statement1 requires all 3 (A, B, and C) to be true.
Statement 2 requires only A and B (but NOT C) to be true.
Statement3 requires only A and C (but NOT B) to be true.

Now, since ANY of these statements (due to the ORing of them together) returns true, the whole thing returns true. So, what is the ONLY condition that MUST ALWAYS BE true?

A is true.

Satement2 can be false as long as Statement3 is true.
Statement3 can be false as long as Statement2 is true.

In other words, as long as EITHER

Activity='::SelectActivity::'
- OR -
Purpose='::SelectPurpose::'

is true, the statement returns true whenever

Name='::SelectName::'

Another, perhaps clearer rendition of the SELECT Statement, which would mean the same thing, would be:

SELECT * FROM Results
WHERE
(Name='::SelectName::')
AND
(Activity='::SelectActivity::' OR Purpose='::SelectPurpose::')

So, let's take a look at a possible list of candidates:

Name Activity Purpose
A 1 W
B 2 X
C 3 Y
D 4 Z



Now, let's test the following:

A, 1, W (true: Name=A, and ((Activity = 1) AND (Purpose = W))))
A, 1, X (true: Name=A and ((Activity=1) OR (Purpose=W))
A, 2, W (true: Name=A and ((Activity=1) OR (Purpose=W))
A, 2, X (false: Name=A, and (Activity <> 1) and (Purpose <>W))

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Show me your certification without works,
and I'll show my certification
*by* my works.