MySQL - #1066 - 不是唯一的表/别名 : 'components' with multiple inner joins

Question

我有此查询，但出现错误 #1066 - 不是唯一的表/别名:'components'。似乎是什么问题？

SELECT COUNT(*) FROM `products`, `components`, `tradeNames` 
INNER JOIN `componentsMap` ON componentsMap.product_id = product.id 
INNER JOIN `components` ON componentsMap.component_id = components.id 
INNER JOIN `tradeNamesMap` ON .tradeNamesMap.product_id = products.id 
INNER JOIN `tradeNames` ON tradeNamesMap.tradeName_id = tradeNames.id 
WHERE (((((LOWER(inci) LIKE '%abies%') 
          OR (trade_name.LOWER(name) LIKE '%abies%')) 
         OR (components.LOWER(no_cas)='abies')) 
        OR (components.LOWER(no_einecs)='abies')) 
       OR (components.LOWER(name)='abies'))
AND (`published`=1) 
ORDER BY `trade_name`.`name` DESC

Answer 1

您不需要在 INNER JOIN 之前列出表。事实上，永远不要在 FROM 子句中使用逗号。所以:

SELECT COUNT(*)
FROM `products`
INNER JOIN `componentsMap` ON componentsMap.product_id = product.id 
INNER JOIN `components` ON componentsMap.component_id = components.id 
INNER JOIN `tradeNamesMap` ON tradeNamesMap.product_id = products.id 
INNER JOIN `tradeNames` ON tradeNamesMap.tradeName_id = tradeNames.id 
WHERE (((((LOWER(inci) LIKE '%abies%') 
          OR (trade_name.LOWER(name) LIKE '%abies%')) 
         OR (components.LOWER(no_cas)='abies')) 
        OR (components.LOWER(no_einecs)='abies')) 
       OR (components.LOWER(name)='abies'))
AND (`published`=1) 
ORDER BY `trade_name`.`name` DESC;

由于 COUNT()，上述查询只返回一行。 order by 表明您确实需要每个 trade_name.name 的此信息。如果是这样，您需要一个 GROUP BY:

SELECT tn.name, COUNT(*)
FROM `products` p INNER JOIN
     `componentsMap cm
     ON cm.product_id = p.id INNER JOIN
     `components` c
     ON cm.component_id = c.id INNER JOIN
     `tradeNamesMap` tnm
     ON tnm.product_id = p.id INNER JOIN
     `tradeNames` tn
     ON tnm.tradeName_id = tn.id 
WHERE ((LOWER(inci) LIKE '%abies%') OR
       (tn.LOWER(name) LIKE '%abies%') OR
       (c.LOWER(no_cas)='abies') OR
       (c.LOWER(no_einecs)='abies') OR
       (c.LOWER(name)='abies')
      ) AND
      (`published` = 1) 
GROUP BY tn.name
ORDER BY tn.`name` DESC