mysql - 未知列，但该列使用两个内联接存在

Question

我有这个 SQL:

select * 
from products_to_mz
join mz_categories on products_to_mz.product_type = mz_categories.id
join products_to_mz as ptm on products.products_id = ptm.product_id
order by products_to_mz.product_type

我正在尝试从数据库中获取如下内容:

product_id, product_type_id, product_type_name

(这些列有不同的名称，但我想让您知道这有什么意义)。

问题是，当 products.products_id 实际存在时，我收到此 SQL 错误。

SQL ERROR: #1054 - Unknown column 'products.products_id' in 'on clause'

以下是第二个连接表的结构:

products_to_mz 结构:

产品结构:

Answer 1

在您的查询中产品别名未在任何地方定义，假设它是为 products_to_mz 那么查询应该是

SELECT *
FROM   products_to_mz products
       JOIN mz_categories ON  products_to_mz.product_type = mz_categories.id
       JOIN products_to_mz AS ptm ON  products.products_id = ptm.product_id
ORDER BY
       products.product_type

或

 SELECT *
    FROM   products_to_mz 
           JOIN mz_categories ON  products_to_mz.product_type = mz_categories.id
           JOIN products ON  products.products_id = ptm.product_id
    ORDER BY
           products_to_mz .product_type