Need some help with SQL Join... Cant seem to come up witha working solution

TerrorRonin · 11-12-2008, 10:35 PM

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.

Example: $game['genre'] now will represent 'Volcom', because the replace $game['genre'] pulled up the `genres` table and found Volcom to match the `id`

Wildhoney · 11-13-2008, 02:16 AM

Is this just a case of adding unique names? Such as like in the following example:

sql Code:

SELECT
    myTable1.myColumn1 AS MyCol1,
    myTable2.myColumn1 AS MyCol2
FROM
    myTable1
INNER JOIN
    ...

TerrorRonin · 11-13-2008, 10:34 AM

Well, the data inside of the tables looks like this

Code:

`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)

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?

sarmenhb · 11-15-2008, 04:27 AM

the format is

select column from table1 inner join table2 on table1.column=table2.column (where table1.column = ....)

the stuff in the parenthesis is optional