Solved

SELECT DISTINCT AND JOIN

Posted on 2006-11-01
15
3,371 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
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
 

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now