mysql - 加入 3 个表并防止重复详细信息

Question

你好，我有这个 3 表，我想制作一个没有重复细节的连接表。

User Table

userLRN userLevel

user_information Table
userInfoID  userLRN userFirstname userLastname userMiddlename userSex userContactNumber userYearGraduated

workinfo Table
workinfoID userLRN workOccupation

设计:

User Table is to one user_information
user_information is to many workinfo

我尝试了这个 SQL 查询:

SELECT userLRN, userFirstname, userLastname, userMiddlename, userSex,
userContactNumber, userYearGraduated, workOccupation 
From user_information 
INNER JOIN users on user_information.userLRN = users.userLRN 
INNER JOIN workinfo on workinfo.userLRN = user_information.userLRN 
WHERE users.userLevel = users

我得到了多个名字和 ID。我想将相同的 id 和名称与不同的工作结合起来。请帮我解决这个问题。

我想要这个输出: Image Output

Answer 1

基于您的 SQLFiddle和下面的评论，试试:

SELECT user_information.userLRN, userFirstname, userLastname, 
group_concat(workOccupation) as workStuff 
From user_information 
INNER JOIN users on user_information.userLRN = users.userLRN 
INNER JOIN workinfo on workinfo.userLRN = user_information.userLRN 
WHERE users.userLevel = 'user' 
group by user_information.userLRN, userFirstname, userLastname;


+---------+---------------+--------------+-------------------+
| userLRN | userFirstname | userLastname | workStuff         |
+---------+---------------+--------------+-------------------+
|       1 | Al            | Go           | Work1,Work2,Work5 |
|       2 | Megan         | Fox          | Work4             |
+---------+---------------+--------------+-------------------+

关于 group_concat 的 mysql 手册页

题为 The power of MySQL’s GROUP_CONCAT 的 Percona 文章