如何基于主表字段值连接2个不同的表？

Question

我有三张表，分别是menus、products和categories。

menus表有id、item_type（可以是product|category）和item_id。

我想要做的是当menu item_type = product时，将其与products表连接ON products.id = menu.item_id，当item_type = category时，将其与categories连接。

这是我的当前查询：

SELECT m.* FROM menus m 
LEFT JOIN products p ON p.id = m.item_id AND m.item_type = 'product' 
LEFT JOIN categories c ON c.id = m.item_id AND m.item_type = 'category'

查询成功，但在每个菜单中没有看到任何产品或类别数据附加。

我是否遗漏了什么？谢谢。

- wobsoriano

Answer 1

你需要选择你想要的列。你只是从“menus”表中选择列，因此这就是你得到的全部内容。

我建议从其他两个表中选择列，但使用COALESCE()函数将它们组合起来。

SELECT m.*,
       COALESCE(p.name, c.name) as name,  -- or whatever the columns are
       -- repeat for additional columns that you want
FROM menus m LEFT JOIN
     products p
     ON p.id = m.item_id AND m.item_type = 'product' LEFT JOIN
     categories c
     ON c.id = m.item_id AND m.item_type = 'category'

- Gordon Linoff

---

虽然其他答案也能用，但这才是我需要的。我想要将产品或类别名称仅显示为一个字段，使用 COALESCE(p.name, c.name) 作为名称实现了这一点。谢谢！ - wobsoriano

Answer 2

您可以通过使用 SELECT * 或者具体指定需要的字段来选择菜单中的值。

请注意，正确的查询方式是直接使用 * 或者明确指定需要的字段。

SELECT * FROM menus m 
LEFT JOIN products p ON p.id = m.item_id AND m.item_type = 'product' 
LEFT JOIN categories c ON c.id = m.item_id AND m.item_type = 'category'

- Jerome

Answer 3

你需要包括要与之JOIN的表的字段，因为你只选择了m表的字段。

最终，你可能还需要将你的连接设置为OUTER，以便仍然包括不匹配任何两个连接的记录：例如：具有另一个item_type的菜单（这不是你实际使用情况，我现在可以看到）

SELECT m.*, p.*, c.* FROM menus m 
LEFT JOIN products p ON p.id = m.item_id AND m.item_type = 'product' 
LEFT JOIN categories c ON c.id = m.item_id AND m.item_type = 'category'

应该足够返回所有 menu 记录，其中一些仅具有产品信息（类别字段的 NULL 值）反之亦然：

• 当 item_type=category 时，p.* 值为 NULL
• 当 item_type=menu 时，c.* 值为 NULL

- SalDevOps