Hey Again,I've been making great progress but I've hit another road block which a newbie intern like myself can't surpass. What's worse is the fact that no one is in the office today! Maybe someone can point me in the right direction with this SQL:SELECT r.[requestID],r.[requserID],r.[departmentID],CONVERT(CHAR(8),r.[submitDate],10) AS submitDate,CONVERT(CHAR(8),r.[dueDate],10) AS dueDate ,CONVERT(CHAR(8),r.[revisedDueDate],10) AS revisedDueDate,r.[reqStatus],r.[completedDate],d.[departmentName],s.[statusName],u.lastName + ', ' + u.firstName AS submittedBy,ra.userIDFROM tblUserDepartment ud INNER JOIN tblRequest r ON ud.departmentID = r.departmentIDINNER JOIN tblDepartment d ON r.departmentID = d.departmentIDINNER JOIN tblStatus s ON r.reqStatus = s.statusIDINNER JOIN tblUser u ON r.requserID = u.userIDLEFT JOIN tblRequestAssignee ra ON r.requestID = ra.requestIDWHERE ud.userID = @userID
This works great except for one thing. In tblRequestAssignee, you have 1 primary assignee and can have several other assignees (that are not primary). This is denoted by a bit field "isPrimaryAssignee" in tblRequestAssignee. When I run the query, I see every request I want to but it duplicates requests with more than one assignee. What I'm trying to do is make only the primaryAssignee display if there is one. If there's not, then null is displayed (which is already happening).Like I said, the query is mostly working right except for this duplicate record that displays when there's 2 assignees. Any help would once again be greatly appreciated.
Eliminate Duplicate Record(s)?
Posted - 2007-06-22 : 12:19:37
