mysql switch to alternative table depending on conditions

Question

Hi all,
this post is a long one sorry for that but the problem is complex too.

Using mysql and php,
I can solve my problem with 3 sql requests, but not in 1, nor in 2.

So that's why I post here :
does any of you know a way to do it in less than 3 strokes ?

(sorry for my english)

First let me explain the context :
it's about theme park !

So there is the theme park owner,
and he owns a theme park with different zones, and each zone contains games.
Each game can be setup with different settings, according to a game manager (the human responsible for the game).

Here is what the interesting part of the schema looks like
there are 5 tables

themeparkzone
--name (varchar)

themeparkzone_has_game_with_manager
--themeparkzone_id (pk)
--game_id (pk)
--manager_id (fk)
--game_settings (text)

themeparkzone_has_game
--themeparkzone_id (pk)
--game_id (pk)
--game_settings (text)

game
--name (varchar)

manager
--name (varchar)

Two tables may need more explanations :

themeparkzone_has_game_with_manager and themeparkzone_has_game.

themeparkzone_has_game may hold the default settings for the games, if the themepark owner wrote them.

Then themeparkzone_has_game_with_manager holds the game settings that the game_manager has (or has not) setup,
and those settings should override the default settings from the themeparkzone_has_game table when set up.

Because the themepark owner wants his themepark to be very attractive, he plans to change the settings every day,
and here is the convention that he and his team will have to deal with :

to decide which game settings will apply,

we have the following vars are given at startup :
- the manager_id : for example 22.
- the themeparkzone ids for today, for example 12,13,14

So the final goal is :
for the given manager_id, and for the given themeparkzone_ids,
find out the name and settings of the games,

given that :
if a setting is set in the themeparkzone_has_game_with_manager it will apply
else
if a setting is set in the themeparkzone_has_game it will apply
else
there is no game.

That's the problem I have,
and because I could'nt do it within 1 request,
I spent a first request to loop the themeparkzone_ids,
so that the problem now is to find the game name and settings for a given manager_id and a given themeparkzone_id.

At this point, I can do it in two requests, but I wondered if one can do it in one.

My requests are :

SELECT g.name,w.game_settings
FROM game g
INNER JOIN themeparkzone_has_game_with_manager w on w.game_id=g.id
WHERE w.manager_id=22
AND w.themeparkzone_id in(12,13,14)

and

SELECT g.name,h.game_settings
FROM game g
INNER JOIN themeparkzone_has_game h on h.game_id=g.id
WHERE h.themeparkzone_id in(12,13,14)

Foreach of them, I put the resulting array in a php array and then mix them according to the conventions.

So I was wondering if there was one way to solve the conditions in a mysql request instead of doing it with php.

A request that would do something like this :
foreach (themeparkzone_id and manager_id)
if exists a matching setting in themeparkzone_has_game_with_manager
takeit,
else
if exists a matching setting in themeparkzone_has_game
takeit,
else
takenothing.

Hope someone understood what I tried to explain.

Answer 1

Considering your database layout, I think it might be better to change some things.

I would put themeparkzone_has_game_with_manager and themeparkzone_has_game_with_manager in one single table. Then I would create a new table games_managers that establishes a connection of which managers can manage which games.

Then I think, it should be possible to do it with fewer requests.

Note that you have to work out the details for yourself, i.e. you might specify a flag for each game if it needs to have a manager or not, etc.

Answer 2

You could do this using LEFT JOIN and COALESCE.

SELECT
    g.name,
    COALESCE(w.game_settings, h.game_settings)
FROM
    game AS g
    INNER JOIN themeparkzone_has_game AS h ON h.game_id = g.id
    LEFT JOIN themeparkzone_has_game_with_manager AS w ON
        w.game_id = g.id AND
        w.themeparkzone_id = h.themeparkzone_id AND
        w.manager_id = 22
WHERE
    h.themeparkzone_id IN (12, 13, 14)

Unlike an INNER JOIN, a LEFT JOIN always contains at least one row for each row in the left-hand table, even if there are no rows in the right-hand table matching the join conditions. In this case the columns from the right-hand table will all be NULL. COALESCE() returns the first non-NULL value it is supplied with, so if there are no matching rows in themeparkzone_has_game_with_manager, it will return the game_settings value from themeparkzone_has_game.

Answer 3

hooray,
I finally found a turn around,
it's a little messy for a newbie like me but it works, and …
only the results matter…

Here is the request that corresponds to my needs :
thanx all for your help :

SELECT
coalesce(g.name,g2.name),
coalesce(w.params, h.params)
FROM
themeparkzone z
LEFT JOIN themeparkzone_has_game_with_manager w ON w.themeparkzone_id=z.id AND w.manager_id=2
LEFT JOIN themeparkzone_has_game h on h.themeparkzone_id=z.id
LEFT JOIN game g ON g.id=w.game_id
LEFT JOIN game g2 ON g2.id=h.game_id WHERE z.id in(12,13,14)

So this request fetch result in the themeparkzone_has_game_with_manager table, and if not founded, try to fetch result from the themeparkzone_has_game and if not founded either, still return a null result.
Hope it helps someone.