This content has been marked as final. Show 2 replies
>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.
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)
Visit us for dozens of useful Dreamweaver Extensions.
Partner at Community MX - Extend your knowledge
> 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_numRows = 0