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

TalkPHP (http://www.talkphp.com/forums.php)

- General (http://www.talkphp.com/general/)

- - Need some help with SQL Join... Cant seem to come up witha working solution (http://www.talkphp.com/general/3599-need-some-help-sql-join-cant-seem-come-up-witha-working-solution.html)

TerrorRonin

11-12-2008 10:35 PM

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

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

All times are GMT. The time now is 03:50 PM.