0

I have a table structure as follows..

enter image description here

and here is sample data...

tblTeam
----------------------------------
Name                                      TeamID
Royal Challengers Bangalore                 1
Chennai Super Kings                         2
Delhi Daredevils                            3
Sunrisers Hyderabad                         4
Kolkata Knight Riders                       5
Mumbai Indians                              6
Kings XI Punjab                             7
Rajasthan Royals                            8
Deccan Chargers                             9
Kochi Tuskers Kerala                       10
Pune Warriors                              11
------------------------------------------------

tblSchedule
------------------------------------------------
ScheduleID  DateTime    Team_1  Team_2  VenuID

1   4/18/08 8:00 PM 1   5   6
2   4/19/08 5:00 PM 2   7   9
3   4/19/08 8:30 PM 3   8   4
4   4/20/08 4:30 PM 5   9   1
5   4/20/08 8:00 PM 1   6   5
6   4/21/08 8:00 PM 8   7   27
7   4/22/08 8:00 PM 3   9   10
8   4/23/08 8:00 PM 2   6   2
9   4/24/08 8:00 PM 8   9   10
10  4/25/08 8:00 PM 6   7   9
11  4/26/08 4:00 PM 5   2   2
12  4/26/08 8:00 PM 1   8   6
-----------------------------------------------

The yellow key in the pic denote primary key and blue one foreign key.

and my requirement is like this....

DateTime                 Team-1                 Team-2        
Apr 8, 2015 8:00:00 PM   Kolkata Knight Riders  Mumbai Indians

Please help to get that o/p...

Raging Bull
  • 18,593
  • 13
  • 50
  • 55
xyz
  • 43
  • 8

2 Answers2

4

Join tblTeam twice with different alias names (T1 & T2):

SELECT ScheduleID,DateTime,T1.Name as [Team-1],T2.Name as [Team-2]
FROM tblSchedule S JOIN
     tblTeam T1 ON S.Team_1=T1.TeamID JOIN 
     tblTeam T2 ON S.Team_2=T2.TeamID
ORDER BY S.ScheduleID

Sample Result:

ScheduleID  DateTime                Team-1                         Team-2
----------------------------------------------------------------------------------------
1           April, 18 2008 20:00:00 Royal Challengers Bangalore    Kolkata Knight Riders
2           April, 19 2008 17:00:00 Chennai Super Kings            Kings XI Punjab
3           April, 19 2008 20:30:00 Delhi Daredevils               Rajasthan Royals
4           April, 20 2008 16:30:00 Kolkata Knight Riders          Deccan Chargers
5           April, 20 2008 20:00:00 Royal Challengers Bangalore    Mumbai Indians

Sample result in SQL Fiddle

Raging Bull
  • 18,593
  • 13
  • 50
  • 55
0

I like to use subquery for this type of problem to avoid the extra joining product.

SELECT 
    CONVERT(varchar(20), DateTime, 100) AS DateTime, 
    (SELECT Name FROM tblTeam WHERE s.Team_1 = TeamID) AS Team-1, 
    (SELECT Name FROM tblTeam WHERE s.Team_2 = TeamID) AS Team-2 
FROM tblSchedule s

Extra reading

Community
  • 1
  • 1
Eric
  • 5,675
  • 16
  • 24