?
Solved

SELECT DISTINCT AND JOIN

Posted on 2006-11-01
15
Medium Priority
?
3,378 Views
Last Modified: 2012-08-13
Hi -
I know there are many questions posted with this topic, but I couldn't find the answer I wanted.  I'm trying to query two joined tables and have a distinct record for each clientid.  Currenty, my query is returning many duplicate with the same clientid number.  Please help me tweak this query to only return distinct records for each client id.  Thanks!

declare @StartDate   datetime,
           @EndDate     datetime

Set @StartDate='2005-10-01 00:00:00.000'
Set @EndDate='2006-12-01 00:00:00.000'

SELECT DISTINCT a.clientid, a.clientname, b.date, b.jobnumber FROM tblClient a INNER JOIN tblScheduleAssignments b
ON a.clientid=b.clientid
where a.officeid = 4 and active = 1 and date >= @startdate and date <= @enddate and clientname NOT LIKE '%CSD%'



0
Comment
Question by:llputney
[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
15 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17850248
SELECT DISTINCT a.clientid, a.clientname, Max(b.date) [date], b.jobnumber FROM tblClient a INNER JOIN tblScheduleAssignments b
ON a.clientid=b.clientid
where a.officeid = 4 and active = 1 and date >= @startdate and date <= @enddate and clientname NOT LIKE '%CSD%'


if this is not working, please give a sample output and expected result
0
 

Author Comment

by:llputney
ID: 17850340
Before I do that query, I want to be able to understand it.  Why use Max(b.date) [date]?  That does not make sense to me at all when I really want to make sure that I don't have duplicate clientid's.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17850399
Since you haven't shown the current results , i assume that the problem duplicates are due to the the date values , that's  why i put the Max which will select the max out of the date column
0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 

Author Comment

by:llputney
ID: 17850588
I ran the query and the first 3 records of the result set looks like this:

clientid     clientname                 date                                         jobnumber
--------     -------------                ------                                        ------------
11411      Handsplash      2005-10-06 00:00:00.000      287803
11411      Handsplash      2005-10-13 00:00:00.000      287804
11411      Handsplash      2005-10-20 00:00:00.000      287805


I want it to look more like this:

clientid     clientname                 date                                         jobnumber
--------     -------------                ------                                        ------------
11411      Handsplash      2005-10-06 00:00:00.000      287803
11429      MMCC            2005-10-06 00:00:00.000       317609

11411      Handsplash      2005-10-20 00:00:00.000      287805
0
 

Author Comment

by:llputney
ID: 17850621
Sorry...didn't finish the above

I want the results to look like this:
clientid     clientname                 date                                         jobnumber
--------     -------------                ------                                        ------------
11411     Handsplash                 2005-10-06 00:00:00.000          287803
11429     MMCC                        2005-10-06 00:00:00.000          317609
10474     Canyon Lake               2005-11-04 00:00:00.000          338816
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17850670
SELECT a.clientid, a.clientname, b.date, Min(b.jobnumber)jobnumber
FROM tblClient a
INNER JOIN tblScheduleAssignments b
ON a.clientid=b.clientid
where a.officeid = 4 and active = 1 and date >= @startdate and date <= @enddate and clientname NOT LIKE '%CSD%'
GROUP BY a.clientid, a.clientname,  b.jobnumber
0
 

Author Comment

by:llputney
ID: 17850709
All job numbers are unique anyway.  I'm going to go ahead and try MIN(a.clientid).  I've already pointed out that I want unique clientid nbrs.
0
 

Author Comment

by:llputney
ID: 17850717
By the way, I went ahead and tried your query and it still came out with duplicate client id's.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17850737
is ClientID and JobNumber  unique ?
0
 

Author Comment

by:llputney
ID: 17850752
ClientID are unique for each company, but the table can have more than one of the same clientid.  JobNumber is unique for every job/clientid - but I took it out as I realized it was not important.

It seems to work with MIN(clientid) but I'm double checking to make sure.
0
 

Author Comment

by:llputney
ID: 17850768
I'm sorry, I take it back.  MIN(clientid) did not work, I still see some duplicate clientid.
0
 

Author Comment

by:llputney
ID: 17851540
I figured it out.  I realized that the date field was causing the problem.  This is what I did:

declare @StartDate   datetime,
           @EndDate     datetime

Set @StartDate='2005-10-01 00:00:00.000'
Set @EndDate='2006-12-31 00:00:00.000'

SELECT DISTINCT a.clientid, a.clientabbrev, a.clientname, a.attn, a.address1, a.address2, a.city, a.state, a.zip
FROM tblClient a
INNER JOIN tblScheduleAssignments b ON a.clientid=b.clientid
where a.officeid = 4 and active = 1 and date >= @startdate and date <= @enddate and clientname NOT LIKE '%CSD%'
GROUP BY a.clientid, a.clientabbrev, a.clientname, a.attn, a.address1, a.address2, a.city, a.state, a.zip
ORDER BY a.clientid

I eliminated jobnumber and date.  The reason why I put it there in the first place was because I just wanted to make sure that the query was using the search criteria (between dates).  I'm not sure why I put job number there.

What I was really trying to do was to grab all businesses that had used our services within the last year for a Christmas mailing list.

Since I answered this question myself and I know it was a bit unfair of me that I wasn't more clearer with my intentions, what should I do with points?  I would like to award points simply for effort, but I cannot "accept" any answers except my own.  What should I do?

Thanks for your help, aneeshattingal!
0
 
LVL 19

Expert Comment

by:folderol
ID: 17852341
Please don't consider any points for this, since the question is answered but I thought this might show you something new.

declare @StartDate   datetime,
        @EndDate     datetime

Set @StartDate='2005-10-01 00:00:00.000'
Set @EndDate='2006-12-31 00:00:00.000'

SELECT DISTINCT
a.clientid, a.clientabbrev, a.clientname, a.attn, a.address1, a.address2, a.city, a.state, a.zip
FROM tblClient a
INNER JOIN
(select distinct clientid from tblScheduleAssignments where [date] between @startdate and @enddate)
as b ON a.clientid=b.clientid
where a.officeid = 4 and a.active = 1 and a.clientname NOT LIKE '%CSD%'

You could also have used EXISTS.  This gets rid of the group by, which might not improve performance, but makes things easier to read.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 18281039
PAQed with points refunded (250)

Computer101
EE Admin
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

777 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