April 2nd, 2013, 07:48 PM
Displaying Child Table Fields
I using the PHPRunner software to build this soccer database at the moment containing thousands of English league matches featuring the two teams, the result etc. It has a relational structure, and I'm almost there in terms of what I want to achieve. But the big problem is that (as you can see in the image below), because I have used a separate table for storing the number of goals a team scores in a match, there is an issue getting this data displayed how I want to.
I want the two teams involved in the match to be displayed side by side, as well as the number of goals they've scored side by side as well. At the moment, there are two entries for every match — one for the home side and one for the away team, which means a lot of repeating data. I only want one entry for one match
As you can see in the first image, the user can click on one of the rows which reveals another table featuring the goals scored and the two team names. Okay, this works, but it's by no means a very effective way of presenting the data.
As you can see in the database structure, there is only one field for team and one field for goals, so I can't find a way of getting the data of two records crammed into one essentially.
So to put simply, how do I get that data from the child table (the goals and two teams) displayed alongside all the normal columns using a MySQL statement?
The database structure:
The SQL query that is currently being used to displayed in the table you see is:
INNER JOIN tblMatchDetails ON tblMatch.MatchID = tblMatchDetails.MatchID
INNER JOIN tblTeam ON tblMatchDetails.TeamID = tblTeam.TeamID
April 2nd, 2013, 08:06 PM
When you want data "alongside" other data, 99% of the time that means a JOIN.
Do two JOINs for the team information: one for the home team and one for the away team. Use whatever information you have to distinguish the two apart - HomeTeam I guess? Including tblTeam that means actually four additional tables.
Side note: I see a lot of *ID fields in that field list. Should there be a few more tables joined in?
/* match */
/* home team */
mdh.Goals AS HomeGoals,
mdh.TeamID AS HomeTeamID,
th.TeamName AS HomeTeamName,
/* away team */
mda.Goals AS AwayGoals,
mda.TeamID AS AwayTeamID,
ta.TeamName AS AwayTeamName
FROM tblMatch m
JOIN tblMatchDetails mdh ON m.MatchID = mdh.MatchID AND mdh.HomeTeam = 1
JOIN tblTeam th ON mdh.TeamID = th.TeamID
JOIN tblMatchDetails mda ON m.MatchID = mda.MatchID AND mda.HomeTeam = 0
JOIN tblTeam ta ON mda.TeamID = ta.TeamID
Comments on this post
April 2nd, 2013, 08:34 PM
That appears to work absolutely fine. Thanks a lot!
Originally Posted by requinix