Queries - And Or

[ldoodle]

Hi,

I have the following query, which changes depending on what options they choose. The page is reposted to itself for request.form etc.

If IsEmpty(Session("ShowHideClosedIncidents")) Or Session("ShowHideClosedIncidents") = "HideClosedIncidents" Then
	If strSanctionsQuery			= "SELECT * FROM tbl_Sanctions " Then
		strSanctionsQuery		= strSanctionsQuery & "WHERE SanctionState = 'Open' "
	Else
		strSanctionsQuery		= strSanctionsQuery & "AND SanctionState = 'Open' "
	End If
End If

If Not IsEmpty(ClassName) Then
	If strSanctionsQuery			= "SELECT * FROM tbl_Sanctions " Then
		strSanctionsQuery		= strSanctionsQuery & "WHERE Class = '" & ClassName & "' "
	Else
		strSanctionsQuery		= strSanctionsQuery & "AND Class = '" & ClassName & "' "
	End If
End If
If Not IsEmpty(StudentSearchText) Then
	If strSanctionsQuery			= "SELECT * FROM tbl_Sanctions " Then
		If Not IsEmpty(StudentSearchForename) And Not IsEmpty(StudentSearchSurname) Then
			strSanctionsQuery	= strSanctionsQuery & "WHERE StudentForename LIKE '%" & StudentSearchForename & "%' AND StudentSurname LIKE '%" & StudentSearchSurname & "%' "
		Else
			strSanctionsQuery	= strSanctionsQuery & "WHERE StudentForename LIKE '%" & StudentSearchText & "%' OR StudentSurname LIKE '%" & StudentSearchText & "%' "
		End If
	Else
		If Not IsEmpty(StudentSearchForename) And Not IsEmpty(StudentSearchSurname) Then
			strSanctionsQuery	= strSanctionsQuery & "AND StudentForename LIKE '%" & StudentSearchForename & "%' AND StudentSurname LIKE '%" & StudentSearchSurname & "%' "
		Else
			strSanctionsQuery	= strSanctionsQuery & "AND StudentForename LIKE '%" & StudentSearchText & "%' OR StudentSurname LIKE '%" & StudentSearchText & "%' "
		End If
	End If
End If
If Not IsEmpty(Gender) Then
	If strSanctionsQuery			= "SELECT * FROM tbl_Sanctions " Then
		strSanctionsQuery		= strSanctionsQuery & "WHERE Gender = '" & Gender & "' "
	Else
		strSanctionsQuery		= strSanctionsQuery & "AND Gender = '" & Gender & "' "
	End If
End If
If Not IsEmpty(YearGroup) Then
	If strSanctionsQuery			= "SELECT * FROM tbl_Sanctions " Then
		strSanctionsQuery		= strSanctionsQuery & "WHERE Year = '" & YearGroup & "' "
	Else
		strSanctionsQuery		= strSanctionsQuery & "AND Year = '" & YearGroup & "' "
	End If
End If
If Not IsEmpty(SoL) Then
	If strSanctionsQuery			= "SELECT * FROM tbl_Sanctions " Then
		strSanctionsQuery		= strSanctionsQuery & "WHERE SoL = '" & SoL & "' "
	Else
		strSanctionsQuery		= strSanctionsQuery & "AND SoL = '" & SoL & "' "
	End If
End If
If Not IsEmpty(LogNumberSearchText) Then
	If strSanctionsQuery			= "SELECT * FROM tbl_Sanctions " Then
		strSanctionsQuery		= strSanctionsQuery & "WHERE LogNumber = '" & LogNumberSearchText & "' "
	Else
		strSanctionsQuery		= strSanctionsQuery & "AND LogNumber = '" & LogNumberSearchText & "' "
	End If
End If
If Not IsEmpty(TeacherInitials) Then
	If strSanctionsQuery			= "SELECT * FROM tbl_Sanctions " Then
		strSanctionsQuery		= strSanctionsQuery & "WHERE TeacherInitials = '" & TeacherInitials & "' "
	Else
		strSanctionsQuery		= strSanctionsQuery & "AND TeacherInitials = '" & TeacherInitials & "' "
	End If
End If

If IsEmpty(SortColumn) And IsEmpty(SortDirection) Then
	SortColumn				= "DateTimeUpdated"
	SortDirection				= "DESC"
End If

strSanctionsQuery				= strSanctionsQuery & " ORDER BY " & SortColumn & " " & SortDirection

As you can see, the first bit checks for a session variable of ShowHideClosedIncidents. If this is empty or set to 'HideClosedIncidents' then it ignores all entries where the SanctionStatus field has a value of Closed. If it's set to ShowClosedIncidents, it show them all. This works ok...

Now, all but the StudentSearchText query adhere to the parent query as I reckon is seeing the OR function in the SQL statement, which in turn, is not 'forcing' it to read the SanctionState = 'Open'.

To summarize, if I search for smith, the SQL statement looks like

SELECT * FROM tbl_Sanctions WHERE SanctionState = 'Open' AND StudentForename LIKE '%smith%' OR StudentSurname LIKE '%smith%' ORDER BY DateTimeUpdated DESC

Is it possible to use the OR function, but in a way that it 'listens' to the preceding WHERE's and AND's

Hope that all makes sense...

Thanks/...

[gil davis]

Parentheses will alter the way the precedence rules are applied. Use them to "rank" the and/or statements in the correct order.

[Nanscombe]

Hi ldoodle,

I'll apologise first for not being an ASP guru, I'm more used to PHP ... anyway.

The code below gradually builds up a Where clause parameter by parameter.

Because the logic around the Name search was complex, I've tried to simplify it by breaking it down into it's component parts and creating the StudentSearchMode; is there some Search Text (SST), is there a Surname search (SSS), is there a Forename search (SSF).

This gives me combinations of options, to which I can apply individual searches.

Below is a line of code which crops up throughout the code.

if strWhereClause != "" Then strWhereClause = strWhereClause & " AND "

What it does is if strWhereClause is not empty then append AND before the next bit of SQL.

The line

If strWhereClause != "" Then strWhereClause = " WHERE " & strWhereClause

prepends WHERE to strWhereClause if it is not empty.

strWhereClause = ""
If IsEmpty(Session("ShowHideClosedIncidents")) Or Session("ShowHideClosedIncidents") = "HideClosedIncidents" Then
 if strWhereClause != "" Then strWhereClause = strWhereClause & " AND "
 strWhereClause = strWhereClause & "SanctionState = 'Open' "
End If
 
If Not IsEmpty(ClassName) Then
 if strWhereClause != "" Then strWhereClause = strWhereClause & " AND "
 strWhereClause = strWhereClause & "Class = '" & ClassName & "' "
End If
 
StudentSearchMode = ""
IF IsEmpty(StudentSearchText) Then
 StudentSearchMode = StudentSearchMode & "---"
Else
 StudentSearchMode = StudentSearchMode & "SST"
End If
 
IF IsEmpty(StudentSearchForename) Then
 StudentSearchMode = StudentSearchMode & "---"
Else
 StudentSearchMode = StudentSearchMode & "SSF"
End If
 
IF IsEmpty(StudentSearchSurname) Then
 StudentSearchMode = StudentSearchMode & "---"
Else
 StudentSearchMode = StudentSearchMode & "SSS"
End If
 
If StudentSearchMode = "------SSS" Then
 if strWhereClause != "" Then strWhereClause = strWhereClause & " AND "
 strWhereClause = strWhereClause & "StudentSurname LIKE '%" & StudentSearchSurname & "%'"
End If
 
If StudentSearchMode = "---SSF---" Then
 if strWhereClause != "" Then strWhereClause = strWhereClause & " AND "
 strWhereClause = strWhereClause & "StudentForename LIKE '%" & StudentSearchForename & "%'"
End If
 
If StudentSearchMode = "---SSFSSS" Then
 if strWhereClause != "" Then strWhereClause = strWhereClause & " AND "
 strWhereClause = strWhereClause & "StudentForename LIKE '%" & StudentSearchForename & "%'"
 if strWhereClause != "" Then strWhereClause = strWhereClause & " AND "
 strWhereClause = strWhereClause & "StudentSurname LIKE '%" & StudentSearchSurname & "%'"
End If
 
If StudentSearchMode = "SST------" Then
 if strWhereClause != "" Then strWhereClause = strWhereClause & " AND "
 strWhereClause = strWhereClause & "StudentForename & ' ' & StudentSurname LIKE '%" & StudentSearchText & "%'"
End If
 
If StudentSearchMode = "SST---SSS" Then
 if strWhereClause != "" Then strWhereClause = strWhereClause & " AND "
 strWhereClause = strWhereClause & "("
 strWhereClause = strWhereClause & "StudentForename & ' ' & StudentSurname LIKE '%" & StudentSearchText & "%'"
 strWhereClause = strWhereClause & " OR "
 strWhereClause = strWhereClause & "StudentSurname LIKE '%" & StudentSearchSurname & "%'"
 strWhereClause = strWhereClause & ")"
End If
 
If StudentSearchMode = "SSTSSF---" Then
 if strWhereClause != "" Then strWhereClause = strWhereClause & " AND "
 strWhereClause = strWhereClause & "("
 strWhereClause = strWhereClause & "StudentForename & ' ' & StudentSurname LIKE '%" & StudentSearchText & "%'"
 strWhereClause = strWhereClause & " OR "
 strWhereClause = strWhereClause & "StudentForename LIKE '%" & StudentSearchForename & "%'"
 strWhereClause = strWhereClause & ")"
End If
 
If StudentSearchMode = "SSTSSFSSS" Then
 if strWhereClause != "" Then strWhereClause = strWhereClause & " AND "
 strWhereClause = strWhereClause & "("
 strWhereClause = strWhereClause & "StudentForename & ' ' & StudentSurname LIKE '%" & StudentSearchText & "%'"
 strWhereClause = strWhereClause & " OR "
 strWhereClause = strWhereClause & "StudentForename LIKE '%" & StudentSearchForename & "%'"
 strWhereClause = strWhereClause & " OR "
 strWhereClause = strWhereClause & "StudentSurname LIKE '%" & StudentSearchSurname & "%'"
 strWhereClause = strWhereClause & ")"
End If
 
If Not IsEmpty(Gender) Then 
 if strWhereClause != "" Then strWhereClause = strWhereClause & " AND "
 strWhereClause = strWhereClause & "Gender = '" & Gender & "' "
endif
 
If Not IsEmpty(YearGroup) Then
 if strWhereClause != "" Then strWhereClause = strWhereClause & " AND "
 strWhereClause = strWhereClause & "Year = '" & YearGroup & "' "
End If
 
If Not IsEmpty(SoL) Then
 if strWhereClause != "" Then strWhereClause = strWhereClause & " AND "
 strWhereClause = strWhereClause & "SoL = '" & SoL & "' "
End If
 
If Not IsEmpty(LogNumberSearchText) Then
 if strWhereClause != "" Then strWhereClause = strWhereClause & " AND "
 strWhereClause = strWhereClause & "LogNumber = '" & LogNumberSearchText & "' "
End If
 
If Not IsEmpty(TeacherInitials) Then
 if strWhereClause != "" Then strWhereClause = strWhereClause & " AND "
 strWhereClause = strWhereClause & "TeacherInitials = '" & TeacherInitials & "' "
End If
 
If strWhereClause != "" Then strWhereClause = " WHERE " & strWhereClause
 
If IsEmpty(SortColumn) And IsEmpty(SortDirection) Then
 SortColumn    = "DateTimeUpdated"
 SortDirection    = "DESC"
End If
 
strSanctionsQuery = "SELECT * FROM tbl_Sanctions" & strWhereClause & " ORDER BY " & SortColumn & " " & SortDirection

I hope this helps.
Regards
Nigel