SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 3 table join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

natsucow
Starting Member

2 Posts

Posted - 07/07/2013 :  14:31:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 07/08/2013 :  00:14:46  Show Profile  Reply with Quote
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

GROUP BY Player_name
--
Chandu

Edited by - bandi on 07/08/2013 00:15:22
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/08/2013 :  01:39:32  Show Profile  Reply with Quote
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
Go to Top of Page

natsucow
Starting Member

2 Posts

Posted - 07/08/2013 :  02:36:47  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/08/2013 :  02:40:12  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000