Table join/database design prob

Question

Table join/database design prob

dcarpenter-708789

SSC Veteran

Points: 219
More actions
October 7, 2008 at 3:40 am

#198944

I have a problem which is prob very simple but it's driving me mad!
I've four tables
UsersTargetsPlacementsPlacementConsultant
UsersTargetidPlacementidPlacementid
UseridUseridPlacementFeeUserid
usernameBoardings
surnameMonth
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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33508 More actions · Answer 1

dcarpenter (10/7/2008)
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

Use the same (combined) columns in you GROUP BY as you use in your SELECT.

GROUP BY u.username +' '+u.surname

instead of username and surname seperated.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I don't think Hanshi's solution is correct. Could you supply some sample data along with the desired result? You may need to use a derived table or a CTE in order to get the data you desire. You could also try using Sum(Distinct value).

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

GSquared SSC Guru Points: 260824 More actions · Answer 3

What relationship is there, if any, between placement fees and boardings?

If there isn't one, you're going to end up with, effectively, a cross-join, which will give you row multiplication.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

dcarpenter-708789 SSC Veteran Points: 219 More actions · Answer 4

There’s isn't a relationship between boarding’s and placementfee.

I'm trying to show what sales people have achieved against what they are targeted to achieve

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Here is how I think I'd solve the problem, but without some data I can't guarantee accuracy:

[font="Courier New"];WITH cteUserTargets AS

(

SELECT

U.userid,

U.username + ' ' + u.surname AS username,

SUM(T.boardings) AS total_boardings

FROM

dbo.users U JOIN

dbo.targets T ON

U.userid = T.userid

GROUP BY

U.userid

),

cteUserPlacements AS

(

SELECT

PC.userid,

COUNT(P.placementid) AS placements,

SUM(P.placementfee) AS total_fees

FROM

dbo.placements P JOIN

dbo.placementconsultant PC ON

P.placementid = PC.placementid

GROUP BY

U.userid

)

SELECT

C.username,

C.totalboardings,

P.placements,

P.total_fees

FROM

cteUserTargets C LEFT JOIN

cteUserPlacements P ON

C.userid = P.userid

[/font]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question