php - 试图更好地理解 MYSQL 子查询并返回多于 1 行

Question

此问题是 Is there a standard way to swap a query from an ID to a different column like 'name'? 的后续问题

这个查询工作正常:

    SELECT manufacturers.id, manufacturers.name, models.model_manufacturer, models.model_part
    FROM models 
    INNER JOIN manufacturers 
    ON manufacturers.id = models.model_manufacturer
    WHERE model_part = (
        SELECT parts.id 
        FROM parts 
        WHERE parts.name = '{$part_name}')
    GROUP BY manufacturers.id
    ORDER BY name ASC

但是这个，当它遇到一个有多个型号的制造商时，会失败并显示“子查询返回多于 1 行”:

SELECT parts.id, models.id, models.model_part, models.model_manufacturer, models.name, models.year, models.weight
    FROM parts
    INNER JOIN models
    ON parts.id = models.model_part
    WHERE model_manufacturer = (
        SELECT manufacturers.id 
        FROM manufacturers 
        WHERE manufacturers.name = '{$manufacturer_name}') 
    && model_part = (
        SELECT parts.id 
        FROM parts 
        WHERE parts.name = '{$part_name}')

我不太理解的是，如果我在工作示例中只运行子查询，或者在第二个示例中单独运行子查询，它们都会返回一个值。如果我运行整个查询，它们都会返回多个值。那么为什么一个错误而另一个没有呢？它在 myPHPAdmin 中运行查询时工作正常，但当我尝试在我的 PHP 文件中执行时失败。

Answer 1

But this one, when it encounters a manufacturer who has more than a single model, fails with "Subquery returns more than 1 row"

MySQL = 运算符可以将 1 个值与另一个值进行比较。当子查询返回更多值，然后返回 1 个值时，它会感到困惑，因为它不知道要与哪个值进行比较。

使用 IN 运算符代替 =:

SELECT parts.id, models.id, 
    models.model_part, models.model_manufacturer, 
    models.name, models.year, models.weight
FROM parts
INNER JOIN models
ON parts.id = models.model_part
WHERE model_manufacturer = (
        SELECT manufacturers.id 
        FROM manufacturers 
        WHERE manufacturers.name = '{$manufacturer_name}') 
    && model_part IN (
        SELECT parts.id 
        FROM parts 
        WHERE parts.name = '{$part_name}')

抱歉，如果我将 IN 运算符放在错误的位置。但我认为，model_part 的第二个子查询是正确的位置。