• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 695
  • Last Modified:

SQL - Avoiding duplicate fields in an outer join

How do I join two tables so that the outer table only joins to the first occurance in the inner table.  In this case the first 4 columns come from the inner table and the outer bale contains hours for each employee by date.

---------
Select A.*, B.Hours FROM A
join table B on
      A.Date= B.Date
and A.EmplNo =B.EmplNo

Instead of returning this as it does now;

Date     EmplNo  Code    Amt       Hours    
7/15        450         1         100        45
7/15        450         2         600        45
7/15        450         3           95        45
7/15        751         1         320        51
7/15        751         3         117        51

I need it to return this;

Date     EmplNo  Code    Amt       Hours
7/15        450         1         100        45
7/15        450         2         600        
7/15        450         3           95        
7/15        751         1         320        51
7/15        751         3         117        

This is part of a procedure for a Crystal Report and i could use a union query but trying to avoid restructuring the  entire procedure.  

Many Thanks
0
dkiam
Asked:
dkiam
2 Solutions
 
lwadwellCommented:
SQL will join to every row that meets the join criteria ... so you need extra predicates and a LEFT JOIN to do what you want.
By the look of it ... Code = 1 seems to be the row you want to join to and if so try:
Select A.*, B.Hours FROM A 
LEFT join table B on A.Code = 1 AND A.Date= B.Date 
and A.EmplNo =B.EmplNo

Open in new window

0
 
dkiamAuthor Commented:
Thanks lwadwell,
Oops that was a coincidence.  The field "code" does not exist in table b.
The only two common and key fields to relate are EmplNo and Date.
Table b fields are Date, EmplNo, and Hours.  
Table A = many  while Table B = one in the relationship.    

So it would need to return something more like this


Date     EmplNo  Code    Amt       Hours
7/15        450         1         100        45
7/15        450         2         600        
7/15        450         3           95        
7/15        751         2         320        51
7/15        751         3         117
0
 
lwadwellCommented:
ahh ... I thought that it might only be a happy coincidence.  The 'code' didn't need to exist in B
OK ... same thing, just need to force it a little
Select A.*, B.Hours 
FROM (select A.*, row_number()over(partition by EmplNo, [date] Order by Code) rn from A) A 
LEFT join B on A.rn = 1 
AND A.Date= B.Date 
and A.EmplNo =B.EmplNo

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
mlmccCommented:
Are you doing any summaries on the hours?

If not you could format the field to suppress if duplicate.

Summaries could be done with running totals which evaluate on change of the employee field

mlmcc
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
dkiamAuthor Commented:
lwadwell solution worked great.<br /><br />Before posting this question, I tried mlmcc  Crystal solution of using suppress if duplicated but  did not find solution to group summaries sum properly and figured handling in the SP was a better solution. <br /><br /> Thanks All .
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now