将一个表连接到另外两个表的 MySQL 查询

Question

我有 3 个表(加星标的 ID 可能为空):

ITEMS
id|name|cost

EVENTS
id|name|date|assignment|items

ASSIGNMENTS
id|name|start|items

其中 ITEMS 包含成本线 - 一个事件可能花费 X，而拥有该事件的任务/项目可能有自己的成本线。事件和分配表中的所有项目都引用为逗号分隔的 ID 列表。

给定一个任务，我想得到

ASSIGNMENTS.NAME|EVENTS.NAME|ITEMS.NAME|ITEMS.COST
Assignment A                 management  10.00
Assignment A     event A     travel exp  60.00
Assignment A     event A     day cost   100.00
Assignment A     event B     day cost    90.00

我尝试了子查询的方式，构建了一个返回 Items 列表的子查询

SELECT assignments.name, events.name, 
       concat(events.items, ",", assignments.items) 
  FROM assignments left join events 
  ON find_in_set(events.assignment, assignments.id) where assignments.id=2

但那样的话我会得到两次列出的作业项，最糟糕的是，我会得到一行(没有事件的作业)，其中的字段以逗号开头。

我也试过两次连接同一个表，但是 MySQL 记得我不能这样做。

关于如何解决这个问题有什么想法吗？

Answer 1

试试这个方法

SELECT assignments.name, events.name, 
   concat(events.items, ",", assignments.items) 
FROM assignments left join events 
ON events.assignment = assignments.id
where assignments.id=2