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”]

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:

               AS gameID, 
                    FROM game 
                    LEFT JOIN team 
                    ON = game.teamOne
                    OR = game.teamTwo
                    WHERE  = 
           = game.teamOne AND = 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 as teamOne,
       teamOne.logo as teamOneLogo,
       teamOne.image as teamOneImage, as teamTwo,
       teamTwo.logo as teamTwoLogo,
       teamTwo.image as teamTwoImage, as gameID
  from team as teamOne
  join game
    on = game.teamOne
  join team as teamTwo
    on game.teamTwo =
 where game.roundID = 1

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