laravel MYSQL 连接同一个表中的多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21189014/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL Join multiple column from same table
提问by Ahmad Hafiz
I'm trying to get skill name for skill1,skill2, & skill3from the table2 by using Join.
我正在尝试使用 Join 从 table2 中获取skill1, skill2, & 的技能名称skill3。
It works fine when Im trying to get skill1alone. But, 1066 Not unique table/aliaserror comes out when I try to get details for the next column.
当我试图skill1独处时它工作正常。但是，当我尝试获取下一列的详细信息时，出现1066 Not unique table/alias错误。
Table 1 (User table)
表1（用户表）
======================================
ID Name skill skill2 skill3
======================================
1 Ed 1 4 3
--------------------------------------
Table 2 (Skill details)
表2（技能详情）
=========================
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:
这是我的代码，我使用的是 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();
回答by erickmcarvalho
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)
回答by user1587985
Same result as @erickmcarvalho query
结果与@erickmcarvalho 查询相同
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
仍然导致 4 个查询，最好重新构造表
