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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
2241 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
52326 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
52326 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  
 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.5 seconds. Powered By: Snitz Forums 2000