Need some help with SQL JOINS

The problem:
Table `games` has the fields `genre`, `publisher`, `developer` which contains `id`s to repsective tables `genres`, `publishers`, and `developers`. I need to create a JOIN (I believe it's an INNER because both need to have values and should have values) that merges the the ids into the respective names.

Basic Database Structure:

`games`
-- `name` (Halo 3)
-- `genre` (1)
-- `developer` (4)
-- `publisher` (3)
-- ...

`developers`
-- `id` (4)
-- `name` (Bungie)
...

`publishers`
-- `id` (3)
-- `name` (Microsoft)
...

`genres`
-- `id` (1)
-- `name` (First Person Shooter)

Code (markup):

And after i've pulled the data, I want to have this come from my fetch_array

$game['name'] = Halo 3
$game['genre'] = First Person Shooter
$game['publisher'] = Microsoft
$game['developer'] = Bungie

Make sense? So what would the MySQL query be for that?

 

Can you not do it with a UNION command?

 

None the less, how do you do it lol

 

first of all, i think you should rethink your database design...

this might work:

SELECT games.name AS name, genres.name AS genre, publishers.name AS publisher, developers.name AS developer FROM games
LEFT JOIN genres ON genres.id = games.genre
LEFT JOIN publishers ON publishers.id = games.publisher
LEFT JOIN developers ON developers.id = games.developer

Code (markup):