combine data from 4 mysql tables and display in php

[theref]

Hi all

 

I have a question which i'm sure is easy to answer, but I can't solve it.  Please help.

 

I have data in the following mysql tables: referee (name, ref_id), games(team_1, team_2, venue, date, competition), official(game_id, ref_id, official_type), official_type(name_id, name).  The links between the tables are:

 

games.game_id - official.game_id

official.official_type - official_type.name_id

official.ref_id - referee.ref_id

 

In the official table there can be up to four rows with the same game_id but all with different official_type's (1,2,3, or 4) which are drawn from official_type.name_id

 

I want to be able to display the following in a php page all on one row, and make it repeat according to the number of rows in the games table:

 

games.game_id, games.team_1, games.team_2, games.venue, games.date, games.competition, referee.name (where official type = 1), referee.name (where official_type = 2), referee.name (where official_type = 3), referee.name (where official_type = 4)

 

So my query is something like (thanks to MS Access - so it may be wrong!)

 

"SELECT ref_games.game_id, ref_games.team_1, ref_games.team_2, ref_games.venue, ref_games.date, ref_games.competition, ref_official.official_type, ref_referee.name FROM ((ref_games INNER JOIN ref_official ON ref_games.game_id = ref_official.game_id) INNER JOIN ref_officialtype ON ref_official.official_type = ref_officialtype.name_id) INNER JOIN ref_referee ON ref_official.ref_id = ref_referee.ref_id
WHERE (((ref_games.game_id)=ref_official.game_id) AND ((ref_official.ref_id)=ref_referee.ref_id))"; 

 

Many thanks in advance

[fenway]

And what does your output currently display?