I'm trying to write a query that pulls details on a game record, as well
as
the officials assigned to the game (up to 4 officials may be assigned to
each game, but that's not always the case).
Game details are in the games table, and assignments are in the
games_referees table (which I
First, your design could be better. You are storing the same data
(referee) in multiple columns. More on that later.
I think the problem with your query is that you are using RIGHT OUTER
JOINS when you can and should be using LEFT JOINS. You want to make
sure you are always keeping the games
SELECT g. * , concat( ref.fname, ' ', ref.lname ) AS ref, concat(
ar1.fname, ' ', ar1.lname ) AS ar1, concat( ar2.fname, ' ', ar2.lname
)
AS ar2, concat( fourth.fname, ' ', fourth.lname ) AS fourth
FROM ( ( ( ( ( ( ( ( games g
RIGHT OUTER JOIN games_referees ref_ass ON ( g.id =
