请求中的 MYSQL 和 IN

Question

在使用 IN 语句使用嵌入在其他 mysql 查询中的 mysql 查询时，我是一个初学者。

我目前有这个查询:

SELECT DISTINCT BorName
FROM Borrower
WHERE BorId IN (
    SELECT Borrower.BorId
    FROM Loan
    WHERE Loan.BcId IN (
        SELECT BookCopy.BcId
        FROM BookCopy
        WHERE BookCopy.BtId In (
            SELECT BookTitle.BtId
            FROM BookTitle
            WHERE BookTitle.PubId In (
                SELECT Publisher.PubId
                FROM Publisher
                WHERE  `PubName` = CONVERT( _utf8 'Methuen' USING latin1 ) COLLATE latin1_swedish_ci
                )
            )
        )
    );

我基本上是想查明借款人是否从出版商 Methuen 那里借了一本书。我似乎无法弄清楚哪里出了问题，我已经检查了每个单独的语句，它们似乎都适用于所有 IN 语句的整体请求。

谁能找出问题所在？

Answer 1

如建议的那样，与嵌套 IN 相比，JOIN 更简洁，并且可能是一种更有效的查询方式:

SELECT DISTINCT b.BorName
FROM 
    Borrower b
    JOIN Loan l ON l.BorId = b.BorId
    JOIN BookCopy bc ON bc.BcId = l.BcId
    JOIN BookTitle bt ON bt.BtId = bc.BtId
    JOIN Publisher p ON p.PubId = bt.PubID
WHERE
    p.PubName = CONVERT( _utf8 'Methuen' USING latin1 ) COLLATE latin1_swedish_ci

此外，我认为您的第一个子查询存在问题:

SELECT Borrower.BorId
FROM Loan
WHERE Loan.BcId IN...

我相信应该是:

SELECT Loan.BorId
FROM Loan
WHERE Loan.BcId IN...