Solved

Getting duplicates in a query.

Posted on 2008-06-26
3
187 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:dsabine
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 21878112
SELECT distinct
   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
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 21878114
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
0
 

Author Closing Comment

by:dsabine
ID: 31471147
.....Oh yeah....Distinct....not "Unique" Thanks!
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question