x

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.

Sports_Name

Id |  Name
---------
1     Football
2     Cricket
3     Hockey   
4     Tenis


Teams

Id  |  Sport_Id | Team_Name
----------------------------
101      1         Manchester United
103      2         Australia
104      2         India
109      1         Real Madrid
110      3         New Zeland


Player_Name

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..

**Football**    
Manchester United -Rooney
Manchester United -Van Persie
Real Madrid -Ronaldo

**Cricket**
India-Tendulkar
Australia-Ponting

**Hockey**
New Zeland -Simond
and something similar for tenis
more ▼

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

fashraf gravatar image

fashraf
418 12 14 20

(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

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1834
x36

asked: Sep 04, 2012 at 08:07 AM

Seen: 628 times

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