Solved

returning distinct value in MS SQL left join Query

Posted on 2013-10-29
22
403 Views
Last Modified: 2013-11-25
I want to return unique O.Quote value, right now I have the following query which returned duplicate O,quote. (book1.xls)
My question is how can I get the O.Quote to show only distinct value? (book2.xls)

select O.quote, TP.ljob, closedate, ARJOBPrice, AIlabor, mc_AICOSTMAT,arcommamt,
dc_AICOSTMAT, po_DMPOCost, ih_poCost,COALESCE(b.client_no,tp.fcustno) AS fcustno,
Q1,Q2,Q3,Q4,COALESCE(b.client_name,TP.fcompany) AS fcompany,
COALESCE(B.sales_rep,TP.fsalespn) as fsalespn,COALESCE(B.salespn,TP.salespn) as salespn
from temp_profitability TP
full outer join xx_client_budget_data B  on TP.fsalespn = B.sales_rep and TP.fcustno = B.client_no
left join xxSALESPN_NTUSER S ON  S.salespnno = B.sales_rep
left join temp_quote TQ on TP.fsalespn=TQ.fsalespn and TP.fcustno = TQ.fcustno
left join (select distinct sum(quote)as Quote, fcustno, fsalespn from bbjthead where closedate is null group by fsalespn, fcustno
) O on TP.fsalespn = O.fsalespn and TP.fcustno = O.fcustno
Book1.xls
Book2.xls
0
Comment
Question by:HemlockPrinters
  • 10
  • 8
  • 2
  • +2
22 Comments
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
You need to use Distinct with the result set of both joined tables.

for tables a(a, ...), b(a, b, ...)

select distinct A.a, b.b 
from a left join b on a.a=b.a;

Open in new window

0
 

Author Comment

by:HemlockPrinters
Comment Utility
thanks hnasr, but this doesn't work
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
List joined tables. Reduce columns used to concentrate on distinct issue.
0
 

Author Comment

by:HemlockPrinters
Comment Utility
I am thinking to use max(O.quote) with group by, but not sure how to rewrite the query
0
 
LVL 5

Expert Comment

by:dannygonzalez09
Comment Utility
ignore this
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Please remove the word distinct here, it is not helping at all.
SELECT DISTINCT -- there is NO (none, zip, nada) point in distinct here!
        SUM(quote) AS Quote
      , fcustno
      , fsalespn
FROM bbjthead
WHERE closedate IS NULL
GROUP BY --<< because group by creates unique rows ( and does it before distinct is evaluated too)
        fsalespn
      , fcustno

Open in new window

will comment further later.

background:
temp_profitibility is generated by a stored procedure I believe see Q_28194544

issues with numbers of records between budgets and sales people see Q_28270193
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
The only difference between Book2 Shhet1 & Book4 Sheet1 is that job 178093 has a blank quote in book2, but a quote of 21669 in book4. NOt exactly sure how the 2 sets of output were generated but that suggests to me a problem with joins.

Without sample records from every table involved, there is some guesswork involved in any suggestion.  In your original you are using a full outer join and a series of coalesce functions to arrive at fsalespn and fcustno, so it may not make sense trying to join via those values until after the full outer join has been performed. With that in mind I'm suggesting the following:
SELECT
          O.quote
        , ljob
        , closedate
        , ARJOBPrice
        , AIlabor
        , mc_AICOSTMAT
        , arcommamt
        , dc_AICOSTMAT
        , po_DMPOCost
        , ih_poCost
        , AS fcustno
        , Q1
        , Q2
        , Q3
        , Q4
        , fcompany
        , fsalespn
        , salespn
FROM (
        SELECT
                TP.ljob
              , closedate
              , ARJOBPrice
              , AIlabor
              , mc_AICOSTMAT
              , arcommamt
              , dc_AICOSTMAT
              , po_DMPOCost
              , ih_poCost
              , COALESCE(b.client_no, tp.fcustno)    AS fcustno
              , Q1
              , Q2
              , Q3
              , Q4
              , COALESCE(b.client_name, TP.fcompany) AS fcompany
              , COALESCE(B.sales_rep, TP.fsalespn)   AS fsalespn
              , COALESCE(B.salespn, TP.salespn)      AS salespn
        FROM temp_profitability TP
                FULL OUTER JOIN xx_client_budget_data B
                        ON TP.fsalespn = B.sales_rep
                                AND TP.fcustno = B.client_no
                LEFT JOIN xxSALESPN_NTUSER S
                        ON S.salespnno = B.sales_rep
                LEFT JOIN temp_quote TQ
                        ON TP.fsalespn = TQ.fsalespn
                                AND TP.fcustno = TQ.fcustno
      ) AS FJ
        LEFT JOIN (
                        SELECT
                                SUM(quote) AS Quote
                              , fcustno
                              , fsalespn
                        FROM bbjthead
                        WHERE closedate IS NULL
                        GROUP BY
                                fsalespn
                              , fcustno
                 ) AS O
                     ON FJ.fsalespn = O.fsalespn
                           AND FJ.fcustno = O.fcustno
;

Open in new window

Does this make any difference to the outcome?

If the problem isn't assisted I'm afraid my suggestion is that you provide sample data for all of the tables (names may be scrubbed for privacy) so a better evaluation an be performed.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
sorry, regarding these left joins:
                LEFT JOIN xxSALESPN_NTUSER S
                        ON S.salespnno = B.sales_rep
                LEFT JOIN temp_quote TQ
                        ON TP.fsalespn = TQ.fsalespn
                                AND TP.fcustno = TQ.fcustno

it might be worthwhile moving this to after the full join too but as not all fields are aliased I wasn't certain what could be moved or not. (tip: alias the fields in the selection list)

and I'm not sure what purpose temp_quote performs, and that left join might either be removed or also moved after the full join.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Can you define the criteria for not returning quote 21669 with ljob value of 178093 but retrieving quote 21669 with ljob value of 177431 (such as latest closedate perhaps)?
0
 

Author Comment

by:HemlockPrinters
Comment Utility
Thanks PortletPaul. it doesnot work.

The result of the following query is shown in Return1:
select  sum(quote)as Quote, fcustno, fsalespn from bbjthead where closedate is null and fsalespn = '110' group by fsalespn, fcustno

The result of this following query is shown in Return2:
select * from temp_profitability where fsalespn = '110'

the result of left join is shown in return3:
select O.QUOTE, * from  temp_profitability TP
LEFT JOIN (select  sum(quote)as Quote, fcustno, fsalespn from bbjthead where closedate is null and fsalespn = '110' group by fsalespn, fcustno ) AS O
ON  TP.fsalespn = O.fsalespn and TP.fcustno = O.fcustno WHERE TP.fsalespn = '110'
return1.xls
return2.xls
return3.xls
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
The following extract from Result 3 (on the left) when compared to Result 1 (on the right)
is exactly what one would expect from the join based on fcustno & fsalespn

Only if your quote has a job number would you expect a "unique" quote per row in result 3
result 3 where quote exists      result 1    
-----------------------------    ------------------------
QUOTE  fcustno fsalespn  ljob    Quote  fcustno  fsalespn
295411  789    110    176930     295411  789    110     matches to 2 rows by fcustno
295411  789    110    177884     2005    1015   110 
0       3661   110    178417     0       1313   110
0       3661   110    178173     0       3661   110     matches to 9 rows by fcustno
0       3661   110    178183     955     3972   110     matches to 7 rows by fcustno
0       3661   110    177201     20587   4844   110
0       3661   110    177204     0       4988   110     matches to 1 rows by fcustno
0       3661   110    177862     107     5818   110
0       3661   110    177544     2533    10811  110     matches to 1 rows by fcustno
0       3661   110    177545     0       10916  110
0       3661   110    176940     0       12327  110
955     3972   110    177803     6696.43 12711  110
955     3972   110    177304     28286   13009  110
955     3972   110    178150     74      13094  110
955     3972   110    177753     0       13210  110
955     3972   110    177651     500     13429  110
955     3972   110    177675     2092    13734  110
955     3972   110    176184     0       14600  110     matches to 1 rows by fcustno
0       4988   110    174792     808.92  14942  110     matches to 6 rows by fcustno
2533    10811  110    178055     1333    15281  110
0       10916  110    176988     943     15706  110
0       14600  110    176557        
808.92  14942  110    178048        
808.92  14942  110    178050        
808.92  14942  110    178066        
808.92  14942  110    178071        
808.92  14942  110    177307        
808.92  14942  110    177332			

Open in new window

IF your quote table has some reference to job, then that left join would look like this (but you have to provide the correct field name):
LEFT JOIN (
                SELECT
                        SUM(quote) AS Quote
                      , fcustno
                      , fsalespn
                      , ljob            --<< add job
                FROM bbjthead
                WHERE closedate IS NULL
                GROUP BY
                        fsalespn
                      , fcustno
                      , ljob            --<< add job
         ) AS O
             ON FJ.fsalespn = O.fsalespn
                   AND FJ.fcustno = O.fcustno
                   AND FJ.ljob = O.ljob --<< add job

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:HemlockPrinters
Comment Utility
thanks PortletPaul.
I understand the query returned the exact result as it should.
what i need is how to rewrite this query to get the distinct Quote value.
something like:
select Max(O.quote), * from existing query group by....
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I'm sorry but I don't quite follow what you are asking for

often words are the real enemy, and a small sample of data can be used to illustrate

are you saying you want to reduce the numbers of rows in the output? like this:
QUOTE   fcustno     fsalespn     ljob
295411  789     110       176930
295411  789     110       177884
0       3661     110       178417
0       3661     110       178173
0       3661     110       178183
0       3661     110       177201
0       3661     110       177204
0       3661     110       177862
0       3661     110       177544
0       3661     110       177545
0       3661     110       176940
955     3972     110       177803
955     3972     110       177304
955     3972     110       178150
955     3972     110       177753
955     3972     110       177651
955     3972     110       177675
955     3972     110       176184
0       4988     110       174792
2533    10811    110       178055
0       10916    110       176988
0       14600    110       176557
808.92  14942    110       178048
808.92  14942    110       178050
808.92  14942    110       178066
808.92  14942    110       178071
808.92  14942    110       177307
808.92  14942    110       177332
               
reduced rows:
QUOTE   fcustno fsalespn     ljob
295411  789     110       176930
0       3661    110       176940
955     3972    110       176184
0       4988    110       174792
2533    10811   110       178055
0       10916   110       176988
808.92  14942   110       177307

Open in new window

0
 

Author Comment

by:HemlockPrinters
Comment Utility
Sorry that I didn't state clearly.
what I want is to replace duplicate quote values to 0, but keep same rows.
QUOTE	ljob	fcustno	FCompany	fsalespn
NULL	177409	13544	STEVENS VIRGIN                          	110
NULL	177410	14409	BELLADONNA BREAST IMAGING CENTER PLLC   	110
0	178417	3661	ALPHA TECHNOLOGIES LTD.                 	110
NULL	177471	14833	DEAD FAMOUS                             	110
955	177803	3972	BLUESHORE FINANCIAL                     	110
NULL	177960	572	LTP SPORTS GROUP INC.                   	110
NULL	175250	3066	PRESENTATION HOUSE GALLERY              	110
0	178173	3661	ALPHA TECHNOLOGIES LTD.                 	110
955	177304	3972	BLUESHORE FINANCIAL                     	110
NULL	178181	15301	WESTWARD ADVISORS LTD.                  	110
NULL	178182	15301	WESTWARD ADVISORS LTD.                  	110
0	178183	3661	ALPHA TECHNOLOGIES LTD.                 	110
NULL	177975	6238	HARPER GREY LLP                         	110
NULL	178045	13385	FORESTETHICS ADVOCACY                   	110
955	178150	3972	BLUESHORE FINANCIAL                     	110
808.92	178048	14942	CENTURY GROUP                           	110
808.92	178050	14942	CENTURY GROUP                           	110
955	177753	3972	BLUESHORE FINANCIAL                     	110
2533	178055	10811	GINSING LLC                             	110
NULL	178129	13544	STEVENS VIRGIN                          	110
NULL	177592	13537	BCMA DBA PHYSICIAN INFORMATION          	110
808.92	178066	14942	CENTURY GROUP                           	110
808.92	178071	14942	CENTURY GROUP                           	110
NULL	177649	14409	BELLADONNA BREAST IMAGING CENTER PLLC   	110
NULL	177650	13385	FORESTETHICS ADVOCACY                   	110
955	177651	3972	BLUESHORE FINANCIAL                     	110
NULL	176792	4811	TORRE & TAGUS DESIGNS LTD.              	110
NULL	176372	6238	HARPER GREY LLP                         	110
0	177201	3661	ALPHA TECHNOLOGIES LTD.                 	110
0	177204	3661	ALPHA TECHNOLOGIES LTD.                 	110
NULL	177217	6238	HARPER GREY LLP                         	110
808.92	177307	14942	CENTURY GROUP                           	110
NULL	177428	14409	BELLADONNA BREAST IMAGING CENTER PLLC   	110
0	177862	3661	ALPHA TECHNOLOGIES LTD.                 	110
NULL	177713	572	LTP SPORTS GROUP INC.                   	110
0	177544	3661	ALPHA TECHNOLOGIES LTD.                 	110
0	177545	3661	ALPHA TECHNOLOGIES LTD.                 	110
NULL	176480	1324	HEMLOCK PRINTERS LTD.                   	110
NULL	176922	13544	STEVENS VIRGIN                          	110
295411.02	176930	789	STORMTECH PERFORMANCE APPAREL LTD.      	110

Open in new window


should be like
QUOTE	ljob	fcustno	FCompany	fsalespn
0	178417	3661	ALPHA TECHNOLOGIES LTD.                 	110
0	178173	3661	ALPHA TECHNOLOGIES LTD.                 	110
0	178183	3661	ALPHA TECHNOLOGIES LTD.                 	110
0	177201	3661	ALPHA TECHNOLOGIES LTD.                 	110
0	177204	3661	ALPHA TECHNOLOGIES LTD.                 	110
0	177862	3661	ALPHA TECHNOLOGIES LTD.                 	110
0	177544	3661	ALPHA TECHNOLOGIES LTD.                 	110
0	177545	3661	ALPHA TECHNOLOGIES LTD.                 	110
0	178048	14942	CENTURY GROUP                           	110
0	178050	14942	CENTURY GROUP                           	110
0	178066	14942	CENTURY GROUP                           	110
0	178071	14942	CENTURY GROUP                           	110
808.92	177307	14942	CENTURY GROUP                           	110
0	177803	3972	BLUESHORE FINANCIAL                     	110
0	177304	3972	BLUESHORE FINANCIAL                     	110
0	178150	3972	BLUESHORE FINANCIAL                     	110
0	177753	3972	BLUESHORE FINANCIAL                     	110
955	177651	3972	BLUESHORE FINANCIAL                     	110
2533	178055	10811	GINSING LLC                             	110
295411.02	176930	789	STORMTECH PERFORMANCE APPAREL LTD.      	110
NULL	177409	13544	STEVENS VIRGIN                          	110
NULL	177410	14409	BELLADONNA BREAST IMAGING CENTER PLLC   	110
NULL	177471	14833	DEAD FAMOUS                             	110
NULL	177960	572	LTP SPORTS GROUP INC.                   	110
NULL	175250	3066	PRESENTATION HOUSE GALLERY              	110
NULL	178181	15301	WESTWARD ADVISORS LTD.                  	110
NULL	178182	15301	WESTWARD ADVISORS LTD.                  	110
NULL	177975	6238	HARPER GREY LLP                         	110
NULL	178045	13385	FORESTETHICS ADVOCACY                   	110
NULL	178129	13544	STEVENS VIRGIN                          	110
NULL	177592	13537	BCMA DBA PHYSICIAN INFORMATION          	110
NULL	177649	14409	BELLADONNA BREAST IMAGING CENTER PLLC   	110
NULL	177650	13385	FORESTETHICS ADVOCACY                   	110
NULL	176792	4811	TORRE & TAGUS DESIGNS LTD.              	110
NULL	176372	6238	HARPER GREY LLP                         	110
NULL	177217	6238	HARPER GREY LLP                         	110
NULL	177428	14409	BELLADONNA BREAST IMAGING CENTER PLLC   	110
NULL	177713	572	LTP SPORTS GROUP INC.                   	110
NULL	176480	1324	HEMLOCK PRINTERS LTD.                   	110
NULL	176922	13544	STEVENS VIRGIN                          	110

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
so,
From this
808.92    178048    14942    CENTURY GROUP    110
808.92    178050    14942    CENTURY GROUP    110
808.92    178066    14942    CENTURY GROUP    110
808.92    178071    14942    CENTURY GROUP    110
808.92    177307    14942    CENTURY GROUP    110

To this:
0         178048    14942    CENTURY GROUP    110
0         178050    14942    CENTURY GROUP    110
0         178066    14942    CENTURY GROUP    110
0         178071    14942    CENTURY GROUP    110
808.92    177307    14942    CENTURY GROUP    110

Open in new window

so you want the quote on the largest of the job numbers only?

by the way, I don't regard this as "accurate"
the data levels are "mis-matched" and the quote of 808.92 cannot be guaranteed to belong to job 177307
0
 

Author Comment

by:HemlockPrinters
Comment Utility
yes, that's what I want.  
the quote of 808.92 doesn't have to belong to job 177307,  
how about the last line or  maximum number of job? actually it's ok to belong to any job.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I'll think on it, but keep in mind that if you change the order of presentation the location of the quote could look illogical. I can't do this immediately - sorry.
0
 

Author Comment

by:HemlockPrinters
Comment Utility
thanks. I need to use it in a Crystal report. i don't have to show the quote for each job. I only need sum of quote value for each company, so that's why it doesn't matter which job the quote value belongs to.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
try this please:
SELECT
          O.quote
        , ljob
        , closedate
        , ARJOBPrice
        , AIlabor
        , mc_AICOSTMAT
        , arcommamt
        , dc_AICOSTMAT
        , po_DMPOCost
        , ih_poCost
        , AS fcustno
        , Q1
        , Q2
        , Q3
        , Q4
        , fcompany
        , fsalespn
        , salespn
FROM (
        SELECT
                TP.ljob
              , closedate
              , ARJOBPrice
              , AIlabor
              , mc_AICOSTMAT
              , arcommamt
              , dc_AICOSTMAT
              , po_DMPOCost
              , ih_poCost
              , COALESCE(b.client_no, tp.fcustno)    AS fcustno
              , Q1
              , Q2
              , Q3
              , Q4
              , COALESCE(b.client_name, TP.fcompany) AS fcompany
              , COALESCE(B.sales_rep, TP.fsalespn)   AS fsalespn
              , COALESCE(B.salespn, TP.salespn)      AS salespn
              , row_number() over (partition by COALESCE(b.client_no, tp.fcustno), COALESCE(B.sales_rep, TP.fsalespn)
                                   order by TP.ljob DESC) as rn
        FROM temp_profitability TP
                FULL OUTER JOIN xx_client_budget_data B
                        ON TP.fsalespn = B.sales_rep
                                AND TP.fcustno = B.client_no
                LEFT JOIN xxSALESPN_NTUSER S
                        ON S.salespnno = B.sales_rep
                LEFT JOIN temp_quote TQ
                        ON TP.fsalespn = TQ.fsalespn
                                AND TP.fcustno = TQ.fcustno
      ) AS FJ
        LEFT JOIN (
                        SELECT
                                SUM(quote) AS Quote
                              , fcustno
                              , fsalespn
                        FROM bbjthead
                        WHERE closedate IS NULL
                        GROUP BY
                                fsalespn
                              , fcustno
                 ) AS O
                     ON FJ.fsalespn = O.fsalespn
                           AND FJ.fcustno = O.fcustno
                           AND ZJ.rn = 1
;

Open in new window

my previous comments about where to include the left joins (lines 45 and 47) still apply, they might be moved until after the full join is performed - I cannot test this.
0
 

Author Comment

by:HemlockPrinters
Comment Utility
sorry, SQL server is 2000, Row_Number() doesn't work in 2000 version
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
please include this in every question you raise for sql 2000 - it's very important to know.

I'm unable to amend suggestion at this time
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
Why can't this need be solved in Crystal Reports? (instead of jiggery pokery at the SQL end)

Have you considered this possibility?
e.g. you would output this quote data in a section header or footer, which would output the value just once pre section (and it will not matter then if it is repeated in the sql result)
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

744 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

15 Experts available now in Live!

Get 1:1 Help Now