Group By Three Tables.

I have three tables which i have joined ..but the manner in which i have to display them is a little difficult for me to understand.I am trying to group them and not getting the desired result.

 Id |  Name
 1     Football
 2     Cricket
 3     Hockey   
 4     Tenis
 Id  |  Sport_Id | Team_Name
 101      1         Manchester United
 103      2         Australia
 104      2         India
 109      1         Real Madrid
 110      3         New Zeland
 Id  |  Team_Id |  Player_Name
 1       101         Rooney
 2       104         Tendulkar
 3       103         Ponting
 4       109         Ronaldo
 5       101         Van Persie
 6       110         Simond

I need to display this information in the following manner..

 Manchester United -Rooney
 Manchester United -Van Persie
 Real Madrid -Ronaldo
 New Zeland -Simond

and something similar for tenis

more ▼

asked Sep 04, 2012 at 08:07 AM in Default

avatar image

538 17 21 29

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The formatting of the data would be something that should be handled by your front-end application, rather than by SQL Server. The query to return the data you require is straightforward:

 declare @Sports_Name TABLE (ID integer, Sport_Name varchar(250))
 insert into @Sports_Name values (1, 'Football'), (2, 'Cricket'), (3, 'Hockey'), (4, 'Tennis')
 declare @Teams TABLE (ID integer, Sport_ID integer, Team_Name varchar(250))
 insert into @Teams values (101, 1, 'Manchester United'), (103, 2, 'Australia'), (104, 2, 'India'), (109, 1, 'Real Madrid'), (110, 3, 'New Zealand')
 declare @Player_Name TABLE (ID integer, Team_ID integer, Player_Name varchar(250))
 insert into @Player_Name values (1, 101, 'Rooney'), (2, 104, 'Tendulkar'), (3, 103, 'Ponting'), (4, 109, 'Ronaldo'), (5, 101, 'Van Persie'), (6, 110, 'Simond')

 -- OK, so the above query lines create tables for me to replicate your environment.
 -- The query below is the actual JOIN and ORDER that returns the data your 
 --         client app can format.

 SELECT Sport_Name, Team_Name, Player_Name
 FROM @Sports_Name s LEFT JOIN @Teams t on s.ID = t.Sport_ID
 LEFT JOIN @Player_Name p ON t.ID = p.Team_ID
 Order by s.ID
more ▼

answered Sep 04, 2012 at 09:38 AM

avatar image

ThomasRushton ♦♦
41.6k 20 50 53

SELECT s.Name, t.Team_Name, p.Player_Name FROM PlayerName p INNER JOIN Teams t ON t.Id = p.Team_id INNER JOIN Sport_Name s ON s.Id = t.Sport_Id....

I could use this but i need to place the Football ,cricket on the top of a grid..hence i need to group it i guess

Sep 04, 2012 at 11:40 AM fashraf

That sort of thing really should be done by the presentation layer - SSRS, your application, your web page - whatever it is that you're doing to display the data to whoever.

If you have to do this all in a single T-SQL query, then say so (& why) - it'll be a much uglier query!

Sep 04, 2012 at 11:55 AM ThomasRushton ♦♦

Thank you !

Sep 04, 2012 at 01:04 PM fashraf
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 04, 2012 at 08:07 AM

Seen: 860 times

Last Updated: Sep 04, 2012 at 01:04 PM

Copyright 2017 Redgate Software. Privacy Policy