mysql - SQL INNER JOIN/Unknown column C.book in On Clause

Question

https://www.db-fiddle.com/f/nCgygDjwYXZnWQ28LL7kMi/0

目标:

选择拥有不同 ISBN 号的“Scott Kelby”两本书的人的副本所有者

大家好，为了在db-fiddle 上的例子中导出book 表，我实际上使用了一堆内部连接语句。

既然我已经获得了书表，我该如何检查 c.owner 是否拥有这本书的两份副本？

我尝试使用

SELECT DISTINCT 
     C.owner
FROM 
     copy C, 
     copy C1
INNER JOIN (
     SELECT 
          B.authors, 
          B.ISBN13
     FROM 
          book B
INNER JOIN( 
     SELECT 
          B1.authors
     FROM 
          book B1
     GROUP BY 
          B1.authors
     HAVING (COUNT(B1.authors) > 1) 
) B1
ON B.authors = B1.authors) B
ON C.book = B.ISBN13 AND C1.book = B.ISBN13 AND C1.book <> C.book;

但是，如果我尝试在第 2 行代码中引用两个表，我将在 ON 子句中收到错误未知列，迫使我从查询中删除副本 C1。

Answer 1

你可以试试下面-

select owner
from book b inner join copy c
on b.isbn13=c.book
group by owner
having count(distinct isbn13)=2