mysql - 连接多个表时遇到问题

Question

我正在尝试为invoice_line_items 设置一个表，该表链接到一个库存表，该表需要根据库存类型连接到各种表以获取额外字段。

invoice_line_items < inventory_id> JOIN inventory < type = m> JOIN inv_extra_m / < type = d> JOIN inv_extra_d

SELECT DISTINCT l.invoice_line_number
FROM invoice_line_items l
LEFT JOIN inventory i
ON i.inventory_id = l.inventory_id
JOIN (
SELECT i.*
FROM inventory i
JOIN inv_extra_m m ON i.inventory_id = m.inventory_id 
WHERE (i.type = 'a' OR i.type = 'b'))
UNION
SELECT i.*
FROM inventory i
JOIN inv_extra_d d ON i.inventory_id = d.inventory_id 
WHERE i.type = 'd'
) ie ON ie.inventory_id = i.inventory_id 
WHERE l.invoice_id = 1

Answer 1

联合语法不正确，在每个选择部分后分配表名称

    SELECT DISTINCT l.invoice_line_number
    FROM invoice_line_items l
    LEFT JOIN inventory i
    ON i.inventory_id = l.inventory_id
    JOIN (
      SELECT i.*
      FROM inventory i
      JOIN inv_extra_m m ON i.inventory_id = m.inventory_id 
      WHERE (i.type = 'a' OR i.type = 'b')
      UNION
      SELECT i.*
      FROM inventory i
      JOIN inv_extra_d d ON i.inventory_id = d.inventory_id 
      WHERE i.type = 'd' 
      ) ie ON ie.inventory_id = i.inventory_id 
    WHERE l.invoice_id = 1