Inner join 2 tables with same column names

Question

I'm working on displaying the achievements from my minecraft server on my website. But I can't get it to work.

function achievements() {
    global $id;
    $sql="SELECT * FROM achievements
           INNER JOIN stats ON achievements.type=stats.type
           INNER JOIN stats ON achievements.block=stats.block
           INNER JOIN stats ON achievements.data=stats.data
           INNER JOIN stats ON achievements.value=stats.value
           WHERE player_id = $id";
    $result=mysql_query($sql) or die(mysql_error());
    $rows=mysql_fetch_array($result);
}

Will I be able to use $rows['achievements.type']; and $rows['stats.type']; to get the column "type" from the selected table, or is there a another way to do it?

The column and table names are defined by the plugin I use, so the names can't be changed.

`SELECT *, stats.type AS stats_type` then access as `$rows['stats_type'];` BUT be careful as $rows['type'] will be from the last table `stats, achievements` — Waygood, Apr 25 '13 at 11:06

score 28 · Accepted Answer · answered Apr 25 '13 at 11:09

the reason why it is not working is because (in my own opinion) the server is a little confused on where how it will handle the columns names properly. In order it to work, add an alias on every table that has the same name that you want to join as well as the columns, eg

SELECT  achievements.*,
        a.Name as TypeName,
        b.Name AS BlockName,
        c.Name as DataName,
        d.Name AS ValueName
FROM    achievements
        INNER JOIN stats a ON achievements.type = a.type
        INNER JOIN stats b ON achievements.block = b.block
        INNER JOIN stats c ON achievements.data = c.data
        INNER JOIN stats d ON achievements.value = d.value
WHERE   player_id = $id

assuming you want to get the names for every specific columns.

score 6 · Answer 2 · answered Apr 25 '13 at 11:08

Give the columns you want to have while you SELECT them an alias as example:

SELECT `achievements`.`type` AS `Achieve-Type`
FROM `achievements`

Now you can get the values like this: $rows['Achieve-Type'];

score 1 · Answer 3 · answered Apr 25 '13 at 11:07

1

It's easier to alias the table names with...

SELECT * FROM achievements AS ac INNER JOIN stats as st

You could even alias the results if you wish so they are more distinguishable when you select them from $rows

answered Apr 25 '13 at 11:07

David

19,577
28
108
128

Inner join 2 tables with same column names

3 Answers3