Join problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Join problem

Author

Topic

DaveC11
Starting Member

43 Posts

Posted - 2008-10-07 : 06:32:25

have a problem which is prob very simple but it's driving me mad!

I've four tables

Users Targets Placements PlacementConsultant
Users Targetid Placementid Placementid
Userid Userid PlacementFee Userid
username Boardings
surname Month

I'm trying to write a query that sums the boarding from the target table and placementfee from the placement table and group it by each user.
The problem I'm having is that the boarding col is being multiplied by the amount of times that the userid appears in the placementconsultant table

select u.username +' '+u.surname,sum(total_boarded), sum(placementfee), count(p.placementid) from targets t
join users u on u.userid = t.userid
left join placementconsultants pc on pc.userid = u.userid
join placements p on p.placementid = pc.placementid
group by u.username,u.surname

Thanks in advance
Dave

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 06:41:19

[code]select u.username +' '+u.surname,sum(total_boarded), pc1.sumplacement, count(p.placementid) from targets t
join users u on u.userid = t.userid
left join
(
select userid,sum(placementfee)as sumplacement
from placementconsultants pc
join placements p on p.placementid = pc.placementid
group by userid)pc1
on pc1.userid = u.userid
group by u.username,u.surname[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources