Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Inner join 2 tables with same column names

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.

like image 721
Lars Kaptein Avatar asked Apr 25 '13 11:04

Lars Kaptein


1 Answers

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.

like image 105
John Woo Avatar answered Sep 23 '22 05:09

John Woo