Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MYSQL Join multiple column from same table [closed]

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();
like image 256
Ahmad Hafiz Avatar asked Jan 17 '14 15:01

Ahmad Hafiz


People also ask

How do I join two columns from the same table in SQL?

The following example shows how to concatenate three different columns: (SELECT id, email1 AS email FROM customer) UNION (SELECT id, email2 AS email FROM customer) UNION (SELECT id, email3 AS email FROM customer) ORDER BY id, email; As you can see, it's important that all the queries return the same columns.

Can join same table twice SQL?

To form a self-join, you specify the same table twice with different table aliases and provide the join predicate after the ON keyword. In this syntax, the table_name is joined to itself using the INNER JOIN clause.

Can we use join on same table?

SQL Server self join syntaxIt helps query hierarchical data or compare rows within the same table. A self join uses the inner join or left join clause. Because the query that uses the self join references the same table, the table alias is used to assign different names to the same table within the query.


2 Answers

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)
like image 101
erickmcarvalho Avatar answered Oct 19 '22 05:10

erickmcarvalho


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

like image 2
user1587985 Avatar answered Oct 19 '22 05:10

user1587985