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();

Not an answer, but I would reorganize my tables first and introduce a third one linking a user ID to a skill ID. Then you are not limited to 3 (or the amount of skill fields in your user table). — jeroen
– jeroen, Commented Jan 17, 2014 at 15:09
@jeroen many to many relationship is not for the faint of heart though — Félix Adriyel Gagnon-Grenier
– Félix Adriyel Gagnon-Grenier, Commented Jan 17, 2014 at 15:10
Think about you have 3 joins and each could be joined User.skil(n) to the skill table by skill.id, hint : each join on user.skil(n) will have a table alias name. Now you can try and show all what you did — Abhik Chakraborty
– Abhik Chakraborty, Commented Jan 17, 2014 at 15:11
If you want to go this way, you need to pay attention to the error message: You need aliases for the table names in the different joins. — jeroen
– jeroen, Commented Jan 17, 2014 at 15:12

erickmcarvalho · Accepted Answer · 2014-01-17 15:42:15Z

19

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)

answered Jan 17, 2014 at 15:42

erickmcarvalho

3492 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pathros Over a year ago

Nice ... However turns out that the performance is very bad when using more than three joins.

user1587985 · Accepted Answer · 2014-01-17 15:53:22Z

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

answered Jan 17, 2014 at 15:53

user1587985

6951 gold badge6 silver badges13 bronze badges

2 Comments

Ahmad Hafiz Over a year ago

Thanks. Yes, you are right, but the JOIN is slightly faster though

Bruce Over a year ago

there is nothing wrong with 3 subqueries. I think restructuring a table vs proper indexing of the subqueries to be a rather silly claim. Just make sure all the subqueries are being run on indexes and there should be no issues.

Collectives™ on Stack Overflow

MYSQL Join multiple column from same table

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related