Query multiple tables - display Team Name based on Team ID

Question

My tables structures are

`TblTeam` (`TeamID`, `TeamName`) VALUES
(1,'India'),
(2,'Pakistan'),
(3,'Brazil')
(4,'Poland');

`TblMatch` (`MatchID`, `MatchDate`, `MatchStart`, `MatchEnd`, `Team1ID`, `Team2ID`) VALUES
(1, '19-11-2014', '12:00:00', '13:00:00', 1, 2),
(2, '19-11-2014', '13:10:00', '14:10:00', 4, 3),
(3, '19-11-2014', '14:20:00', '15:20:00', 1, 3),
(4, '19-11-2014', '15:30:00', '16:30:00', 4, 2),
(5, '20-11-2014', '10:00:00', '11:00:00', 1, 4),
(6, '20-11-2014', '11:10:00', '12:10:00', 3, 4);

INSERT INTO TblScore (ScoreID, TeamID, MatchID, Score) VALUES (1, 1, 1, 5), (2, 2, 1, 6), (3, 4, 2, 15), (4, 3, 2, 26);

I want to display Team Name of (Team1ID, Team2ID) say on 19-11-2014 there are 4 matches so

in php the output should be

Time                   : Between :
12:00:00 - 13:00:00      India v/s Pakistan
13:10:00 - 14:10:00      Poland v/s Brazil

SELECT m.MatchID, m.MatchDate, m.MatchStart, m.MatchEnd, m.Team1ID, m.Team2ID, 
t.TeamID, t.TeamName 
FROM TblMatch m, TblTeam t WHERE m.MatchDate ='$todayis' ORDER BY m.MatchDate

PHP

while($row=mysqli_fetch_array($res)){
    $mid= $row['MatchID'];
    $mdd = $row['MatchDate'];
    $t1 = $row['Team1ID'];
    $t2 = $row['Team2ID'];
    $t1n = $row['TeamName']; 


    echo $t1n . " v/s  . " $t1n ;



 }

Score query does not work 

 $query="SELECT 
 m.MatchID, 
 m.MatchDate, 
 m.Team1ID, 
 m.Team2ID, 
 s.TeamID,
 s.MatchID,
 T1.TeamName as TeamName1, 
 T2.TeamName as TeamName2,
 T1S.Score as Team1Score,
 T2S.Score as Team2Score
 FROM TblMatch m  JOIN TblTeam T1  ON m.Team1ID = T1.TeamID JOIN TblTeam T2  ON m.Team2ID =      T2.TeamID  JOIN TblScore s ON m.Team1ID = T1S.TeamID JOIN TblScore s ON m.Team1ID = T1S.TeamID  JOIN   TblScore s ON m.Team2ID = T2S.TeamID WHERE s.MatchID=$mid
 ";

You would have to join the team table twice. – Strawberry Nov 19 '14 at 09:50 — Strawberry, Nov 19 '14 at 09:50

Serpes · Accepted Answer · 2014-11-19T11:58:24.140

3

You can join your Match table result twice with your team table so you extract the information of the match and the name of each team. After that you only need concatenate the data you fetch from database in PHP.

SELECT 
    m.MatchID, 
    m.MatchDate, 
    m.MatchStart, 
    m.MatchEnd, 
    m.Team1ID, 
    m.Team2ID, 
    T1.Teamname as Teamname1, 
    T2.TeamName as Teamname2
FROM TblMatch M
JOIN TblTeam T1
    ON M.TEAM1ID = T1.TeamID
JOIN TblTeam T2
    ON M.TEAM2ID = T2.TeamID

PHP Code:

while($row=mysqli_fetch_array($res)){
    $mid= $row['MatchID'];
    $mdd = $row['MatchDate'];
    $t1 = $row['Team1ID'];
    $t2 = $row['Team2ID'];
    $t1n = $row['TeamName1']; 
    $t2n = $row['TeamName2']; 

    echo $t1n . " v/s  . " $t2n ;
}

edited Nov 19 '14 at 11:58

answered Nov 19 '14 at 09:52

Serpes

672
4
14

Please add some explanation for the OP. – DontVoteMeDown Nov 19 '14 at 10:21
thanks for replies. but how will I store Team name in variable ? Above code does not work. it outputs India v/s India, Pakistan v/s Pakistan – Danish Nov 19 '14 at 11:57
Thanks. still there is some typo but I corrected. It works like charm :) – Danish Nov 20 '14 at 06:19
hi I have another table for score where I want get score based on match id and team id. code update. can you please check or do you want me to open new question ? – Danish Nov 20 '14 at 08:22
I suppose you just have to make a new join with you new table of results matching the MatchID that must be also in that table. I can help you here if you want but It's becoming like a chat and I think that you can not get into in a chat room of Stackoverflow. So ... I recomend you to write a new question – Serpes Nov 20 '14 at 11:34

score 2 · Answer 2 · edited Nov 19 '14 at 15:34

2

Query:

SELECT  
    m.MatchStart, 
    m.MatchEnd, 
    m.Team1ID, 
    m.Team2ID, 
    T1.TeamName as Teamname1, 
    T2.TeamName as Teamname2
FROM TblMatch m, TblTeam T1, TblTeam T2
 where  m.TEAM1ID = T1.TeamID
and
        m.TEAM2ID = T2.TeamID

php code:

while($row=mysqli_fetch_array($res))
{
$mst= $row['MatchStart'];

$met = $row['MatchEnd'];
$t1n = $row['TeamName1']; 
$t2n = $row['TeamName2']; 

echo 'Time: \t\t between:\r\n';
echo "$mst" . " - " . " $met \t\t" ;
echo "$t1n vs $t2n \r\n";
}

edited Nov 19 '14 at 15:34

Anik Islam Abhi

25,137
8
58
80

answered Nov 19 '14 at 13:00

Domain

11,562
3
23
44

thanks. I am sure it will work but I did not try it. I go with Serpes answer. – Danish Nov 20 '14 at 06:18
voted up :). can you check my updated question for score query. thanks – Danish Nov 20 '14 at 08:31

Query multiple tables - display Team Name based on Team ID

2 Answers2

Linked