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

SQL subquery displays too many results

I am in a bind because I suck at writing queries. Any help will be appreciated. I have a query with some joins and a subquery that doesn't display the data I need. It is either duplicating the data or grabbing data that is not needed. I segregated the join that is causing the issue.

Select EMKey, oa.Num, RB.Grouping4 as CompanyName, RB.EmployeeName, emppr.hiredate, Sum(RB.HOURSBILLED) as HrsBilled, Sum(RB.AMOUNTBILLED) as AmountBilled,
Sum(RB.COSTEARN) as CostEarn, Sum(RB.WCCOST) as WCCost, Sum(RB.OASDICOST) as OASDICost,
Sum(RB.MEDICARECOST) as MedicareCost, Sum(RB.FUTACOST) as FUTACost, Sum(RB.SUICOST) as SUICost, Sum(RB.EICCREDIT) as EICCredit,
Sum(RB.AMOUNTBILLED-RB.COSTEARN-RB.WCCOST-RB.OASDICOST-RB.MEDICARECOST-RB.FUTACOST-RB.SUICOST) as Profit,
Sum(RB.MARKUP) as Markup, Sum(RB.TOTALCOST) as TotalCost,
emppr.SocialSecurityNumber, emp.Address1, emp.City, emp.StateCode, emp.PostalCode
From #Result as RB
left join employeeprmaster as emppr ON emppr.employee_ID = RB.EMKey
left join employeemaster as emp ON emp.employee_ID = RB.EMKey

left join (select employee_id, userdefined1 as Num, assignment_id from orderassignment group by employee_id, assignment_id, userdefined1) as oa ON oa.employee_id = RB.EMKey

Where RB.Earningscode <> 'EXPEN' and RB.Earningscode <> 'EXPEN2' or RB.EarningsCode is Null
Group by RB.EMKey, RB.Grouping3, RB.Grouping4, RB.EmployeeName, emppr.SocialSecurityNumber, emppr.hiredate,
emp.Address1, emp.City, emp.StateCode, emp.PostalCode, oa.Num
order by EMKey
0
Techneut
Asked:
Techneut
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>It is either duplicating the data or grabbing data that is not needed.
Usually when a query returns duplicate data it is because one of the JOINS is missing a column in order to have a unique relationship between the two sets involved.

We'd have to see some sample data to flush this out.
0
 
TechneutAuthor Commented:
I had to find external help with this because of the situation. I found that because of the joins, I had to do multiple join ON connections since there were many to many objects.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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