asked on

Getting duplicates in a query.

I am trying to get a count based on unique case ID but I'm getting duplicates in a query and I'm not sure how to get unique results. Here's my query:

SELECT
tblcases.caseid,
tblCases.CaseDate,
tblCases.CaseStatus
FROM
tblCaseSupport
INNER JOIN
tblCases
ON
tblCaseSupport.CaseID = tblCases.CaseID
INNER JOIN
tblMISUsers
ON
tblCaseSupport.UserID = tblMISUsers.UserID
INNER JOIN
tblCustomers
ON
tblCases.CustomerID = tblCustomers.Customerid
WHERE
tblMISUsers.Name like '%rene%'
AND
tblCases.CaseDate between '01/01/08' and '03/31/08'
or
tblMISUsers.Name like '%bagl%'
AND
tblCases.CaseDate between '01/01/08' and '03/31/08'
or
tblMISUsers.Name like '%rodar%'
AND
tblCases.CaseDate between '01/01/08' and '03/31/08'
ORDER BY
tblcases.caseid asc

The problem is in some cases, two of my reps will be in the same case so those cases show up twice in my results.

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aneesh

SELECT
tblcases.caseid,
MAX(tblCases.CaseDate) CaseDate,
tblCases.CaseStatus
FROM
tblCaseSupport
INNER JOIN
tblCases
ON
tblCaseSupport.CaseID = tblCases.CaseID
INNER JOIN
tblMISUsers
ON
tblCaseSupport.UserID = tblMISUsers.UserID
INNER JOIN
tblCustomers
ON
tblCases.CustomerID = tblCustomers.Customerid
WHERE
tblMISUsers.Name like '%rene%'
AND
tblCases.CaseDate between '01/01/08' and '03/31/08'
or
tblMISUsers.Name like '%bagl%'
AND
tblCases.CaseDate between '01/01/08' and '03/31/08'
or
tblMISUsers.Name like '%rodar%'
AND
tblCases.CaseDate between '01/01/08' and '03/31/08'
group by tblcases.caseid,
tblCases.CaseStatus
ORDER BY
tblcases.caseid asc

David

ASKER

.....Oh yeah....Distinct....not "Unique" Thanks!