MYSQL Join multiple column from same table

Question

I'm trying to get skill name for skill1,skill2, & skill3 from the table2 by using Join.

It works fine when Im trying to get skill1 alone. But, 1066 Not unique table/alias error comes out when I try to get details for the next column.

---

Table 1 (User table)

======================================
ID  Name       skill   skill2   skill3
======================================
1   Ed           1       4       3    
--------------------------------------

Table 2 (Skill details)

=========================
ID  Skill Name
=========================
1   php
2   html
3   css
4   mysql
-------------------------

---

This is what I expect to get:

[name]    => 'Ed'
[skill1]  => 'php'
[skill2]  => 'mysql'
[skill3]  => 'css'

Here's my code, I'm using laravel:

DB::table('table1')
   ->join('table2', function($join)
    {
         $join->on('table1.skill1', '=', 'table2.id');
    })
    ->join('table2', function($join)
    {
         $join->on('table1.skill2', '=', 'table2.id');
    })
    ->join('table2', function($join)
    {
         $join->on('table1.skill3', '=', 'table2.id');
    })
    ->get();

Solution 1

Try this query:

SELECT U.Name AS Name, S1.Skill Name AS Skill1, S2.Skill Name AS Skill2, S3.Skill Name AS Skill3
    FROM table1 U
    JOIN table2 S1 ON (S1.Id = U.skill1)
    JOIN table2 S2 ON (S2.Id = U.skill2)
    JOIN table2 S3 ON (S3.Id = U.skill3)

Solution 2

Same result as @erickmcarvalho query

SELECT Table1.usrname,
(SELECT Table2.skillname FROM Table2 WHERE Table1.skill1 = Table2.Id) As skill1,
(SELECT Table2.skillname FROM Table2 WHERE Table1.skill2 = Table2.Id) As skill2,
(SELECT Table2.skillname FROM Table2 WHERE Table1.skill3 = Table2.Id) As skill3
FROM Table1

Still causes 4 queries, would been better to restructurate tables