SQL - 需要帮助编写/修改查询

I've battling with this for a few days now and as SQL is not something I use too often I can't get my query to work the way I want it to.

我已经和它争斗了几天了，因为SQL不是我经常使用的东西，所以我无法按照我想要的方式工作。

I have a simple test database for tagging records using "toxi" solution (three tables, one acts as a link "ProgramCourses")

我有一个简单的测试数据库，用于使用“toxi”解决方案标记记录（三个表，一个充当链接“ProgramCourses”）

My tables

我的桌子

Program:

 - ProgramID : int
 - ProgramName : varchar

Course:

 - CourseID : int
 - CourseName : varchar

ProgamCourses:

 - ProgramID : int
 - CourseID : int

What I'm trying to do is to collect all tags as string and then add them as a new column to Program table.

我要做的是将所有标签收集为字符串，然后将它们作为新列添加到Program表中。

My query

我的查询

SELECT  * , GROUP_CONCAT(c.CourseName) AS tags_list
FROM `Program` p
LEFT JOIN ProgramCourses AS pc ON p.ProgramID = pc.ProgramID
LEFT JOIN Course AS c ON pc.CourseID = c.CourseID
GROUP BY p.ProgramID
ORDER BY p.ProgramID

this kinda does what I need but it duplicates some columns and result I get is:

这种做了我需要的，但它复制了一些列，我得到的结果是：

ProgramID   ProgramName     ProgramID   CourseID    CourseID    CourseName  tags_list

instead of

代替

ProgramID   ProgramName     CourseName

Any help would be very appreciated. If someone is kind enough to write a query for me could you please write it with full names of tables and columns as this would make it easier for me to understand and adopt for other purposes. Thank you.

任何帮助将非常感激。如果有人能够为我写一个查询，请用全名表格和列来编写，因为这样我就可以更容易理解并采用其他用途。谢谢。

2 个解决方案

#1

DISTINCT c.CourseName Returns a count of the number of rows with different CourseName.

DISTINCT c.CourseName返回具有不同CourseName的行数。

SELECT  * , GROUP_CONCAT(DISTINCT c.CourseName) AS tags_list
FROM `Program` p
LEFT JOIN ProgramCourses AS pc ON p.ProgramID = pc.ProgramID
LEFT JOIN Course AS c ON pc.CourseID = c.CourseID
GROUP BY p.ProgramID
ORDER BY p.ProgramID

For More Info visit it Official

欲了解更多信息，请访问官方

#2

Try this

尝试这个

SELECT  * , GROUP_CONCAT(c.CourseName) AS tags_list
FROM `Program` p
LEFT JOIN ProgramCourses AS pc using(ProgramID)
LEFT JOIN Course AS c using(CourseID)
GROUP BY p.ProgramID
ORDER BY p.ProgramID

#1