Kevin
Mon Apr 10 11:28:41 CDT 2006
This is a multi-part message in MIME format.
------=_NextPart_000_0016_01C65C9A.4D514AD0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
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=20
"WHERE" indicates a test. The WHERE clause must resolve to true.
WHERE
Now I will do some translation:
(Name=3D'::SelectName::' AND Activity=3D'::SelectActivity::' AND =
Purpose=3D'::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=3D'::SelectName::' AND Activity=3D'::SelectActivity::')
A is true AND B is true
And the last:
(Name=3D'::SelectName::' AND Purpose=3D'::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=20
Activity=3D'::SelectActivity::'=20
- OR -
Purpose=3D'::SelectPurpose::'
is true, the statement returns true whenever
Name=3D'::SelectName::'
Another, perhaps clearer rendition of the SELECT Statement, which would =
mean the same thing, would be:
SELECT * FROM Results
WHERE
(Name=3D'::SelectName::')
AND
(Activity=3D'::SelectActivity::' OR Purpose=3D'::SelectPurpose::')
So, let's take a look at a possible list of candidates:
Name Activity Purpose=20
A 1 W=20
B 2 X=20
C 3 Y=20
D 4 Z=20
Now, let's test the following:
A, 1, W (true: Name=3DA, and ((Activity =3D 1) AND (Purpose =3D W))))
A, 1, X (true: Name=3DA and ((Activity=3D1) OR (Purpose=3DW))
A, 2, W (true: Name=3DA and ((Activity=3D1) OR (Purpose=3DW))
A, 2, X (false: Name=3DA, and (Activity <> 1) and (Purpose <>W))
--=20
HTH,
Kevin Spencer
Microsoft MVP
Professional Numbskull
Show me your certification without works,
and I'll show my certification
*by* my works.
"GAA" <GAA@discussions.microsoft.com> wrote in message =
news:F5C1C3BB-8403-4D2D-A92E-3508A6E8156C@microsoft.com...
> This seems so obvious, I'm obviously doing something silly!
>=20
> 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,=20
> whether it be at the beginning, the middle or the end, it simply does =
not=20
> return what I expect!=20
>=20
> The SelectName, SelectActivity and SelectPurpose variables are =
selected by=20
> the user from drop-down lists. If only two conditions are used i.e. =
user only=20
> selects criteria from 2 of the 3 drop-down lists then the correct =
results are=20
> returned. When the user select=06 criteria from all 3 drop-down lists, =
it only=20
> returns results for one of the variables being matched i.e. =
SelectName.
>=20
> For example:
> Say I select 'Greg Aitken' from the SelectName drop-down list, =
'Meeting'=20
> from the SelectActivity drop-down list and 'Teachers' from the =
SelectPurpose=20
> drop-down list it returns all the records which have 'Greg Aitken' in =
the=20
> Name field and ignores the other two conditions, even though there =
definitely=20
> are records that match all 3 criteria.
>=20
> What am I doing wrong? I'm beginning to think it's not my query but=20
> something else?
>=20
> Thanks again
> Greg
>
------=_NextPart_000_0016_01C65C9A.4D514AD0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
<META content=3D"MSHTML 6.00.2900.2802" name=3DGENERATOR>
<STYLE>BODY {
FONT-SIZE: 10pt; FONT-FAMILY: Arial
}
TABLE {
FONT-SIZE: 10pt; FONT-FAMILY: Arial
}
</STYLE>
</HEAD>
<BODY>
<DIV>I'm nost sure what you're expecting, but I can help you understand =
why=20
you're getting the results you're getting, and then you should be able =
to figure=20
it out for yourself.</DIV>
<DIV> </DIV>
<DIV>Let's talk a bit about logic, which is what boolean (and/or) =
statements are=20
all about:</DIV>
<DIV> </DIV>
<DIV>I'm going to substitute some things in the query to make it =
clearer.</DIV>
<DIV> </DIV>
<DIV>This is what is going to happen if the following resolves to =
true:</DIV>
<DIV> </DIV>
<DIV>SELECT * FROM Results </DIV>
<DIV> </DIV>
<DIV>"WHERE" indicates a test. The WHERE clause must resolve to =
true.</DIV>
<DIV> WHERE</DIV>
<DIV> </DIV>
<DIV>Now I will do some translation:<BR><BR>(Name=3D'::SelectName::' AND =
Activity=3D'::SelectActivity::' AND Purpose=3D'::SelectPurpose::')</DIV>
<DIV> </DIV>
<DIV>AND indicates that ALL must resolve to true. Therefore, the =
following can=20
be said to be a translation of this condition:</DIV>
<DIV> </DIV>
<DIV>A is true AND B is true AND C is true</DIV>
<DIV> </DIV>
<DIV>The same for the next:</DIV>
<DIV><BR>(Name=3D'::SelectName::' AND =
Activity=3D'::SelectActivity::')</DIV>
<DIV> </DIV>
<DIV>A is true AND B is true</DIV>
<DIV> </DIV>
<DIV>And the last:</DIV>
<DIV><BR>(Name=3D'::SelectName::' AND =
Purpose=3D'::SelectPurpose::')</DIV>
<DIV> </DIV>
<DIV>A is true AND C is true</DIV>
<DIV> </DIV>
<DIV>Now for the tricky part. "OR" indicates that EITHER statement can =
resolve=20
to true. So, here's a picture of the simplified condition:</DIV>
<DIV> </DIV>
<DIV>(A is true AND B is true AND C is true)</DIV>
<DIV>- OR-</DIV>
<DIV>(A is true AND B is true)</DIV>
<DIV>- OR-</DIV>
<DIV>(A is true AND C is true)</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>But let's simply the equation, by referring to the 3 statements as=20
Statement1, Statement2, and Statement3. Now we have the following:</DIV>
<DIV> </DIV>
<DIV>Statement1 is true OR Statement2 is true OR Statement3 is =
true</DIV>
<DIV> </DIV>
<DIV>In other words, ANY of these statements being true will cause the =
overall=20
condition to return true.</DIV>
<DIV> </DIV>
<DIV>Now, what do all 3 of these statements have in common?</DIV>
<DIV> </DIV>
<DIV>A is true</DIV>
<DIV> </DIV>
<DIV>Therefore, A MUST BE TRUE for the whole thing to return true. If A =
is=20
false, the whole thing is false, because A is ANDed into each of the 3=20
Statements.</DIV>
<DIV> </DIV>
<DIV>What else do the 3 statments have in common?</DIV>
<DIV> </DIV>
<DIV>Nothing.</DIV>
<DIV> </DIV>
<DIV>Statement1 requires all 3 (A, B, and C) to be true.</DIV>
<DIV>Statement 2 requires only A and B (but NOT C) to be true.</DIV>
<DIV>Statement3 requires only A and C (but NOT B) to be true.</DIV>
<DIV> </DIV>
<DIV>Now, since ANY of these statements (due to the ORing of them =
together)=20
returns true, the whole thing returns true. So, what is the ONLY =
condition that=20
MUST ALWAYS BE true?</DIV>
<DIV> </DIV>
<DIV>A is true.</DIV>
<DIV> </DIV>
<DIV>Satement2 can be false as long as Statement3 is true.</DIV>
<DIV>Statement3 can be false as long as Statement2 is true.</DIV>
<DIV> </DIV>
<DIV>In other words, as long as EITHER </DIV>
<DIV> </DIV>
<DIV>Activity=3D'::SelectActivity::' </DIV>
<DIV>- OR -</DIV>
<DIV>Purpose=3D'::SelectPurpose::'</DIV>
<DIV> </DIV>
<DIV>is true, the statement returns true whenever</DIV>
<DIV> </DIV>
<DIV>Name=3D'::SelectName::'</DIV>
<DIV> </DIV>
<DIV>Another, perhaps clearer rendition of the SELECT Statement, which =
would=20
mean the same thing, would be:</DIV>
<DIV> </DIV>
<DIV>SELECT * FROM Results</DIV>
<DIV>WHERE</DIV>
<DIV>(Name=3D'::SelectName::')</DIV>
<DIV>AND</DIV>
<DIV>(Activity=3D'::SelectActivity::' OR =
Purpose=3D'::SelectPurpose::')</DIV>
<DIV> </DIV>
<DIV>So, let's take a look at a possible list of candidates:</DIV>
<DIV> </DIV>
<TABLE id=3Dtable1 style=3D"BORDER-COLLAPSE: collapse" width=3D250 =
border=3D1>
<TBODY>
<TR>
<TD align=3Dmiddle><B>Name</B></TD>
<TD align=3Dmiddle><B>Activity</B></TD>
<TD align=3Dmiddle><B>Purpose</B></TD></TR>
<TR>
<TD>A</TD>
<TD>1</TD>
<TD>W</TD></TR>
<TR>
<TD>B</TD>
<TD>2</TD>
<TD>X</TD></TR>
<TR>
<TD>C</TD>
<TD>3</TD>
<TD>Y</TD></TR>
<TR>
<TD>D</TD>
<TD>4</TD>
<TD>Z</TD></TR></TBODY></TABLE>
<DIV> </DIV>
<DIV> </DIV>
<DIV>Now, let's test the following:</DIV>
<DIV> </DIV>
<DIV>A, 1, W (true: Name=3DA, and ((Activity =3D 1) AND (Purpose =3D =
W))))</DIV>
<DIV>A, 1, X (true: Name=3DA and ((Activity=3D1) OR (Purpose=3DW))</DIV>
<DIV>A, 2, W (true: Name=3DA and ((Activity=3D1) OR =
(Purpose=3DW))</DIV>
<DIV>A, 2, X (false: Name=3DA, and (Activity <> 1) and =
(Purpose=20
<>W))</DIV>
<DIV><BR>-- <BR>HTH,</DIV>
<DIV> </DIV>
<DIV>Kevin Spencer<BR>Microsoft MVP<BR>Professional Numbskull</DIV>
<DIV> </DIV>
<DIV>Show me your certification without works,<BR>and I'll show my=20
certification<BR>*by* my works.<BR></DIV>
<DIV> </DIV>
<DIV>"GAA" <<A=20
href=3D"mailto:GAA@discussions.microsoft.com">GAA@discussions.microsoft.c=
om</A>>=20
wrote in message <A=20
href=3D"news:F5C1C3BB-8403-4D2D-A92E-3508A6E8156C@microsoft.com">news:F5C=
1C3BB-8403-4D2D-A92E-3508A6E8156C@microsoft.com</A>...</DIV>>=20
This seems so obvious, I'm obviously doing something silly!<BR>> =
<BR>>=20
Each condition works separately, and conditions 2 and 3 work fine when =
<BR>>=20
condition 1 isn't included. However as soon as I introduce condition 1, =
<BR>>=20
whether it be at the beginning, the middle or the end, it simply does =
not=20
<BR>> return what I expect! <BR>> <BR>> The SelectName, =
SelectActivity=20
and SelectPurpose variables are selected by <BR>> the user from =
drop-down=20
lists. If only two conditions are used i.e. user only <BR>> selects =
criteria=20
from 2 of the 3 drop-down lists then the correct results are <BR>> =
returned.=20
When the user select=06 criteria from all 3 drop-down lists, it only =
<BR>>=20
returns results for one of the variables being matched i.e. =
SelectName.<BR>>=20
<BR>> For example:<BR>> Say I select 'Greg Aitken' from the =
SelectName=20
drop-down list, 'Meeting' <BR>> from the SelectActivity drop-down =
list and=20
'Teachers' from the SelectPurpose <BR>> drop-down list it returns all =
the=20
records which have 'Greg Aitken' in the <BR>> Name field and ignores =
the=20
other two conditions, even though there definitely <BR>> are records =
that=20
match all 3 criteria.<BR>> <BR>> What am I doing wrong? I'm =
beginning to=20
think it's not my query but <BR>> something else?<BR>> <BR>> =
Thanks=20
again<BR>> Greg<BR>> </BODY></HTML>
------=_NextPart_000_0016_01C65C9A.4D514AD0--