3 table join - 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 2008 Forums

Other SQL Server 2008 Topics

3 table join

Author

Topic

natsucow
Starting Member

2 Posts

Posted - 2013-07-07 : 14:31:06

I have two database tables for a leaderboard that I need to join together. The tables are leadbrdt17 and leadbrdt18. Each table represents one tour of data.

Structure is:
Table - leadbrdt17
leadbdID - primary
tour_day
player_ID - links to foreign players table
bar_ID - links to foreign bars table
visitor_points
ring_points
ftp_points
total_points

Structure for leadbrdt18 is the exact same.

There is also another table that I will need to join. The players table.

Structure for players is:
playerID - primary
player_name

Ok, so I need a query to find the sum of the 'ring points' from both the 'leadbrdt17' and 'leadbrdt18' tables for each player on the players table.

Essentially I need a list like this:

PLAYER RING POINTS
jim 750
bob 500
wendy 100

I must have tried 30 different queries and looked at as many forum examples.

I was thinking a union all with the two leaderboard tables along with a join on the players table... i appreciate any help/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-07-08 : 00:14:46

[code]SELECT Player_name, Sum(ring_Points)
FROM (SELECT player_id, ring_points
FROM leadbrdt17
union all
SELECT player_id, ring_points
FROM leadbrdt17 ) T
JOIN Players p ON T.Player_id = p.Player_id[/code]
GROUP BY Player_name
--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 01:39:32

it should be below provided you need to show all players regardless of whether they've points or not


SELECT p.Player_name, COALESCE(Sum(T.ring_Points),0) AS TotalPoints
FROM Players p
LEFT JOIN
(SELECT player_id, ring_points
       FROM leadbrdt17 
       union all
       SELECT player_id, ring_points
       FROM leadbrdt18 ) T
ON T.Player_id = p.Player_id
GROUP BY p.Player_id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

natsucow
Starting Member

2 Posts

Posted - 2013-07-08 : 02:36:47

Thank both of you for the help. I used bandi's query and it did exactly what I want it to do. Well a couple of changes, but, I got the point.
I don't need to show players without points but I appreciate your efforts visakh16. I have learned a lot from this.
This is what I have now.

SELECT player_name, Sum(ring_points)
FROM (SELECT player_id, ring_points
FROM leadbrdt17
union all
SELECT player_id, ring_points
FROM leadbrdt18 ) T
JOIN players p ON T.player_id = p.playerID
GROUP BY Player_name
ORDER BY Sum(ring_points) DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 02:40:12

quote:
Originally posted by natsucow

Thank both of you for the help. I used bandi's query and it did exactly what I want it to do. Well a couple of changes, but, I got the point.
I don't need to show players without points but I appreciate your efforts visakh16. I have learned a lot from this.
This is what I have now.

SELECT player_name, Sum(ring_points)
FROM (SELECT player_id, ring_points
FROM leadbrdt17
union all
SELECT player_id, ring_points
FROM leadbrdt18 ) T
JOIN players p ON T.player_id = p.playerID
GROUP BY Player_name
ORDER BY Sum(ring_points) DESC

Ok ..No problem

you're welcome
Glad that I could help you in in understanding join basics

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources