• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 698
  • 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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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