Back to basics

Hi All,

Well I have literally been out of the game for about 4 years and I am re-learning this all over again. Trying to get my head around mysqli and OOP etc. - From what I can gather the traditional procedural php days are gooooone! :frowning:

Anyways, I am having an issue where I’m trying to work out if it’s my database schema I need to review or my SELECT statement. Any help would be very much appreciated.

Thanks in advance…

Database schema:
table 1: teams
columns: id; name; logo; image;

table 2: games
columns: id; roundID; teamOne; teamTwo; scoreOne; scoreTwo; (teamOne and teamTwo are numbers (representing the team name that is in the team table)

Now my objective:
Round ID: (eg) 1
[TABLE=“width: 500”]
[TR]
[TD]TeamOne[/TD]
[TD]TeamTwo[/TD]
[/TR]
[TR]
[TD]TeamOne[/TD]
[TD]TeamTwo[/TD]
[/TR]
[TR]
[TD]TeamOne[/TD]
[TD]TeamTwo[/TD]
[/TR]
[/TABLE]

Now for each round the amount of games vary; might be 8, could be 5 etc.

This is the closest I have got to getting the sql:

SELECT
                        team.name, 
                        team.logo, 
                        team.image, 
                        game.id AS gameID, 
                        game.teamOne, 
                        game.teamTwo 
                    FROM game 
                    LEFT JOIN team 
                    ON team.id = game.teamOne
                    OR team.id = game.teamTwo
                    WHERE  = 
                    team.id = game.teamOne AND team.id = game.teamTwo AND game.roundID = 1

This gives me results close to what I’m after but not exact.

My first post after 4 years, I hope this is making sense.

Again thanks for your help on this.

You need to have two instances of the team table in your query, once for team one and once for team two.


select teamOne.name as teamOne,
       teamOne.logo as teamOneLogo,
       teamOne.image as teamOneImage,
       teamtwo.name as teamTwo,
       teamTwo.logo as teamTwoLogo,
       teamTwo.image as teamTwoImage, 
       game.id as gameID
  from team as teamOne
  join game
    on teamOne.id = game.teamOne
  join team as teamTwo
    on game.teamTwo = teamTwo.id
 where game.roundID = 1

Thank you soooo much @swampBoogie. That has worked and has guided me well. Many many thanks.