sql question

[JohnE]

I have the following sql in the code but it is not returning the proper info.
The issue is in the WHERE, AND, AND, OR lines. I am looking to return all
the records that have a WHERE, AND, AND as well as OR. I am missing
something and can't see/tell what it is.

Can someone look at this and see what more is needed?

strSQL = "SELECT BuildKey(ChangeRequestID) As NodeKey, ChangeRequest
As NodeText, BuildKey(Nz(IsChildOf,0)) As BelongTo" & _
" FROM tblChangeRequest" & _
" WHERE IsTopLevel = 1" & _
" AND ChangeRequestStatusID <> 6" & _
" AND ChangeRequestStatusID <> 7" & _
" OR IsChildOf > 0" & _
" UNION ALL SELECT TOP 1 BuildKey('<NULL>') As NodeKey,
'<Un-Assigned>' As NodeText, BuildKey(Nz(IsChildOf,0)) As BelongsTo" & _
" FROM tblChangeRequest" & _
" ORDER BY NodeText"

Thanks.
.... John

 

[John W. Vinson]

I have the following sql in the code but it is not returning the proper info.
The issue is in the WHERE, AND, AND, OR lines. I am looking to return all
the records that have a WHERE, AND, AND as well as OR. I am missing
something and can't see/tell what it is.

Can someone look at this and see what more is needed?

strSQL = "SELECT BuildKey(ChangeRequestID) As NodeKey, ChangeRequest
As NodeText, BuildKey(Nz(IsChildOf,0)) As BelongTo" & _
" FROM tblChangeRequest" & _
" WHERE IsTopLevel = 1" & _
" AND ChangeRequestStatusID <> 6" & _
" AND ChangeRequestStatusID <> 7" & _
" OR IsChildOf > 0" & _
" UNION ALL SELECT TOP 1 BuildKey('<NULL>') As NodeKey,
'<Un-Assigned>' As NodeText, BuildKey(Nz(IsChildOf,0)) As BelongsTo" & _
" FROM tblChangeRequest" & _
" ORDER BY NodeText"

Thanks.
... John

What you may be missing is that if IsChildOf is greater than 0 the expression
"IsChildOf > 0" will be true, and you'll totally ignore all the other
criteria. <Expr1> OR <Expr2> will be TRUE if either Expr1 is true, *or* if
Expr2 is true - no matter how complex each expression is.

You don't indicate what values you want, nor what values you are getting, and
your "Have a WHERE, AND, AND as well as OR" is not a valid expression in
Boolean algebra (or even in English, for that matter). Could you perhaps give
a little "truth table" indicating what combinations of IsTopLevel,
ChangeRequestStatusID and IsChildOf should be retrieved and which should be
excluded?

 

[JohnE]

Mr Vinson, thanks for the reply. This sql is populating the treeview control
from the table. The treeview is to show only active/open change requests.
- The IsTopLevel = 1 is just that, the top level. Branches can be added to
any top level CR. The 1 designates that it is a top level CR.
- The status lines are removing the released and void statuses. This would
be for the IsTopLevel. If the top level isn't showing, any branches for that
top level will not show.
- The IsChildOf brings in the children of the top level or any of its
branches. So if a CR does not have a number in this field, it is not
assigned to a branch of the top level.

A CR could have a 1 for top level, a 4 for the status, and 0 for IsChildOf
since it is a top level.
A CR could have a 0 for top level, a 5 for the status, and (some #) for
IsChildOf indicating it is a branch to some higher ranking CR.

Hope this isn't confusing.

 

[John W. Vinson]

Mr Vinson, thanks for the reply. This sql is populating the treeview control
from the table. The treeview is to show only active/open change requests.
- The IsTopLevel = 1 is just that, the top level. Branches can be added to
any top level CR. The 1 designates that it is a top level CR.
- The status lines are removing the released and void statuses. This would
be for the IsTopLevel. If the top level isn't showing, any branches for that
top level will not show.
- The IsChildOf brings in the children of the top level or any of its
branches. So if a CR does not have a number in this field, it is not
assigned to a branch of the top level.

A CR could have a 1 for top level, a 4 for the status, and 0 for IsChildOf
since it is a top level.
A CR could have a 0 for top level, a 5 for the status, and (some #) for
IsChildOf indicating it is a branch to some higher ranking CR.

That helps explain the structure... but please answer the rest of the
question. Given a particular combination of values, what result do you want?
What results are you getting that you DON'T want, and what results are you not
getting that you do?