2 Replies Latest reply on Oct 3, 2007 8:53 AM by Newsgroup_User

    SQL statement Advice Please

    Level 7
      Hi - Nice to see the forums back :-)
      Ok i have quite a complex question here, but hope some clever peep can help
      me out.
      I have a search page with four methods of searching the database.
      The code used on the results page is below.

      I've created a three table database - author, department, and instructions
      I've created relationships with these tables using authorID and DepartmentID
      connecting to the instructions table.
      So far so good - as you can see from the SQL below.

      The problem is that some of the instructions are applicable to more than one
      department and to solve this they have been entered into the database
      several times, but with the relevant DepartmentID.
      Now if the user decides to search by author, all of the instructions are
      found including the duplicated ones for each department. i.e.

      Authorname1, instruction1, department1
      Authorname1, instruction1, department2
      Authorname1. instruction1, department3

      All i want diaplaying is the one instrcution by that author and not all for
      each department.

      Hope this makes some sense!!!

      Any idea how to solve this issue?

      Thanks in advance - - -


      <%
      Dim RSresults__MMColParam1
      RSresults__MMColParam1 = "0"
      If (Request("author") <> "") Then
      RSresults__MMColParam1 = Request("author")
      End If
      %>
      <%
      Dim RSresults__MMColParam2
      RSresults__MMColParam2 = "0"
      If (Request("dept") <> "") Then
      RSresults__MMColParam2 = Request("dept")
      End If
      %>
      <%
      Dim RSresults__MMColParam3
      RSresults__MMColParam3 = "0"
      If (Request("winumber") <> "") Then
      RSresults__MMColParam3 = Request("winumber")
      End If
      %>
      <%
      Dim RSresults__MMColParam4
      RSresults__MMColParam4 = "0"
      If (Request("keywords") <> "") Then
      RSresults__MMColParam4 = Request("keywords")
      End If
      %>
      <%
      Dim RSresults
      Dim RSresults_numRows

      Set RSresults = Server.CreateObject("ADODB.Recordset")
      RSresults.ActiveConnection = MM_wiConn_STRING
      RSresults.Source = "SELECT DISTINCT author.authorID AS author_authorID,
      author.authorname, department.departmentID AS department_departmentID,
      department.departmentname, wi.wiID, wi.wi_no, wi.authorID AS wi_authorID,
      wi.departmentID AS wi_departmentID, wi.work_instruction FROM department
      INNER JOIN (author INNER JOIN wi ON author.[authorID] = wi.[authorID]) ON
      department.[departmentID] = wi.[departmentID] WHERE authorname = '" +
      Replace(RSresults__MMColParam1, "'", "''") + "' OR departmentname = '" +
      Replace(RSresults__MMColParam2, "'", "''") + "' OR wi_no = '" +
      Replace(RSresults__MMColParam3, "'", "''") + "' OR work_instruction LIKE '%"
      + Replace(RSresults__MMColParam4, "'", "''") + "%' ORDER BY wi_no ASC"
      RSresults.CursorType = 0
      RSresults.CursorLocation = 2
      RSresults.LockType = 1
      RSresults.Open()

      RSresults_numRows = 0
      %>


        • 1. Re: SQL statement Advice Please
          bregent Most Valuable Participant
          >All i want diaplaying is the one instrcution by that author and not all for each department.

          Use the Distinct keyword and do not include department in the result set.
          • 2. Re: SQL statement Advice Please
            Level 7
            Andy,
            I'm not sure, but I think you said that you have multiple entries that
            are identical except for the departmentid? Is this the case? Could you
            post your table schema? (which would clear up any questions about how
            you did your setup)

            Paul Davis
            http://www.kaosweaver.com/
            Visit us for dozens of useful Dreamweaver Extensions.

            http://www.communitymx.com/
            Partner at Community MX - Extend your knowledge

            Andy wrote:
            > Hi - Nice to see the forums back :-)
            > Ok i have quite a complex question here, but hope some clever peep can help
            > me out.
            > I have a search page with four methods of searching the database.
            > The code used on the results page is below.
            >
            > I've created a three table database - author, department, and instructions
            > I've created relationships with these tables using authorID and DepartmentID
            > connecting to the instructions table.
            > So far so good - as you can see from the SQL below.
            >
            > The problem is that some of the instructions are applicable to more than one
            > department and to solve this they have been entered into the database
            > several times, but with the relevant DepartmentID.
            > Now if the user decides to search by author, all of the instructions are
            > found including the duplicated ones for each department. i.e.
            >
            > Authorname1, instruction1, department1
            > Authorname1, instruction1, department2
            > Authorname1. instruction1, department3
            >
            > All i want diaplaying is the one instrcution by that author and not all for
            > each department.
            >
            > Hope this makes some sense!!!
            >
            > Any idea how to solve this issue?
            >
            > Thanks in advance - - -
            >
            >
            > <%
            > Dim RSresults__MMColParam1
            > RSresults__MMColParam1 = "0"
            > If (Request("author") <> "") Then
            > RSresults__MMColParam1 = Request("author")
            > End If
            > %>
            > <%
            > Dim RSresults__MMColParam2
            > RSresults__MMColParam2 = "0"
            > If (Request("dept") <> "") Then
            > RSresults__MMColParam2 = Request("dept")
            > End If
            > %>
            > <%
            > Dim RSresults__MMColParam3
            > RSresults__MMColParam3 = "0"
            > If (Request("winumber") <> "") Then
            > RSresults__MMColParam3 = Request("winumber")
            > End If
            > %>
            > <%
            > Dim RSresults__MMColParam4
            > RSresults__MMColParam4 = "0"
            > If (Request("keywords") <> "") Then
            > RSresults__MMColParam4 = Request("keywords")
            > End If
            > %>
            > <%
            > Dim RSresults
            > Dim RSresults_numRows
            >
            > Set RSresults = Server.CreateObject("ADODB.Recordset")
            > RSresults.ActiveConnection = MM_wiConn_STRING
            > RSresults.Source = "SELECT DISTINCT author.authorID AS author_authorID,
            > author.authorname, department.departmentID AS department_departmentID,
            > department.departmentname, wi.wiID, wi.wi_no, wi.authorID AS wi_authorID,
            > wi.departmentID AS wi_departmentID, wi.work_instruction FROM department
            > INNER JOIN (author INNER JOIN wi ON author.[authorID] = wi.[authorID]) ON
            > department.[departmentID] = wi.[departmentID] WHERE authorname = '" +
            > Replace(RSresults__MMColParam1, "'", "''") + "' OR departmentname = '" +
            > Replace(RSresults__MMColParam2, "'", "''") + "' OR wi_no = '" +
            > Replace(RSresults__MMColParam3, "'", "''") + "' OR work_instruction LIKE '%"
            > + Replace(RSresults__MMColParam4, "'", "''") + "%' ORDER BY wi_no ASC"
            > RSresults.CursorType = 0
            > RSresults.CursorLocation = 2
            > RSresults.LockType = 1
            > RSresults.Open()
            >
            > RSresults_numRows = 0
            > %>
            >
            >