www.webdeveloper.com
Results 1 to 3 of 3

Thread: Queries - And Or

  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    220

    Queries - And Or

    Hi,

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

    Code:
    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
    Code:
    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/...

  2. #2
    Join Date
    Nov 2002
    Posts
    4,473
    Parentheses will alter the way the precedence rules are applied. Use them to "rank" the and/or statements in the correct order.

  3. #3
    Join Date
    Nov 2006
    Location
    Kent, UK
    Posts
    90
    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.

    Code:
    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
    Last edited by Nanscombe; 03-08-2007 at 04:24 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles