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

user_9574351 0
Viewed 153

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

Goal:

Select copy.owner of people that own both books by "Scott Kelby" with different ISBN numbers

Hey guys, in order to derive the book table in the example on db-fiddle, I had actually used a bunch of inner join statements.

Now that I have acquired the book table how can I check that the c.owner owns both copies of the book?

I tried to use

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;

However, if I try to reference to two table in the 2nd line of code, i will get the error unknown column in ON clause forcing me to remove copy C1 from the query.

inner-join join mysql
1 Answers

You can try below -

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