MySQL:如何使用 INNER JOIN 进行转换

Question

是否可以使用INNER JOIN编写此查询？

SELECT links.product_id AS entity_id, products.sku, links.`linked_product_id` AS     linked_entity_id, products2.`sku` AS linked_skus
FROM catalog_product_entity AS products,
    catalog_product_link AS links,
    catalog_product_entity AS products2
WHERE links.`product_id` = products.`entity_id`
    AND links.`linked_product_id` = products2.`entity_id`

此外，catalog_product_link 有一个列 type_id，我只想获取具有 type_id 值 1 的行最后有 AND Catalog_product_link.type_id = 1 条件，但它不起作用。如何仅获取 type_id=1 的记录？

我遇到一个问题，当我将 WHERE links.type_id=1 添加到语句中时，结果出现错误。 product_id 下的所有内容都是相同的数字(相同的entity_id/product_id)，并且 sku 下的所有内容均为 NULL。不过，linked_product_id 和 linked_skus 是正确的。

更多信息:

catalog_product_entity

| entity_id | sku | ... |
-------------------
|    1      | abc |     |
|    2      | qwe |     |
|    3      | yui |     |

catalog_product_link

| product_id (same as entity_id) | linked_product_id | type_id |
----------------------------------------------------------------
|               1                |         5         |    1    |
|               1                |         6         |    1    |
|               2                |         1         |    2    |
|               3                |         1         |    1    |
|               3                |         2         |    1    |
|               3                |         4         |    1    |

Answer 1

类似...

SELECT
    p.sku,
    l.product_id AS entity_id, l.linked_product_id AS linked_entity_id
FROM catalog_product_entity AS p
     INNER JOIN catalog_product_link AS l
         ON p.entity_id= l.product_id
WHERE l.type_id = 1