#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0

    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?

    h ttp://oi49.tinypic.com/2im3uq.jpg

    The database structure:

    h ttp://oi45.tinypic.com/r0tnq9.jpg

    The SQL query that is currently being used to displayed in the table you see is:

    Code:
    SELECT
    tblMatch.MatchID,
    tblMatch.MatchDate,
    tblMatch.MatchAttendance,
    tblMatch.MatchTime,
    tblMatch.DivisionID,
    tblMatch.StadiumID,
    tblMatch.SeasonID,
    tblMatch.RefereeID,
    tblMatch.StatusID,
    tblMatchDetails.Goals,
    tblMatchDetails.HomeTeam,
    tblTeam.TeamName,
    tblMatchDetails.TeamID
    FROM tblMatch
    INNER JOIN tblMatchDetails ON tblMatch.MatchID = tblMatchDetails.MatchID
    INNER JOIN tblTeam ON tblMatchDetails.TeamID = tblTeam.TeamID
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,115
    Rep Power
    9398
    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.

    With aliases,
    Code:
    SELECT
    	/* match */
    	m.MatchID,
    	m.MatchDate,
    	m.MatchAttendance,
    	m.MatchTime,
    	m.DivisionID,
    	m.StadiumID,
    	m.SeasonID,
    	m.RefereeID,
    	m.StatusID,
    	/* 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
    Side note: I see a lot of *ID fields in that field list. Should there be a few more tables joined in?

    Comments on this post

    • supermessiah agrees : Very good answer to my problem.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0
    Originally Posted by requinix
    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.

    With aliases,
    Code:
    SELECT
    	/* match */
    	m.MatchID,
    	m.MatchDate,
    	m.MatchAttendance,
    	m.MatchTime,
    	m.DivisionID,
    	m.StadiumID,
    	m.SeasonID,
    	m.RefereeID,
    	m.StatusID,
    	/* 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
    Side note: I see a lot of *ID fields in that field list. Should there be a few more tables joined in?
    That appears to work absolutely fine. Thanks a lot!

IMN logo majestic logo threadwatch logo seochat tools logo