SQL How to select rows based on values from multiple rows

Question

Hi I ran into a problem regarding the following tables

       CREATE TABLE BOOKS ( 
          Title varchar(255),
          Year INT,
          Type ENUM("Horror", "Fantasy"),
          Pages INT,
          PRIMARY KEY (Title, Year)
        );
        
        CREATE TABLE AUTHORS(
          Title varchar(255), 
          Year INT, 
          PlaceOfBirth varchar(255), 
          AuthorName varchar(255),
          PRIMARY KEY (Title, Year, PlaceOfBirth ),
          FOREIGN KEY (Title, Year) REFERENCES BOOKS(Title, Year)
        );

Now i want to query all Authors who only have horror books with 40 pages and more.

SELECT a.AuthorName, b.Pages FROM AUTHORS a INNER JOIN BOOKS b ON a.Title=b.Title AND a.Year=b.Year
WHERE  b.Type="Horror" AND b.Pages > 40

The problem is now I get those authors which have written horror books with more than 40 pages but they could also have horror books with less than 40 pages. I Want those Authors which only wrote horror books that are 40 pages or longer.

Answer 1

If you want the name of authors that have written at least one horror book and whose all such books have more than 40 pages, you can use group by and having:

select a.authorname
from authors a 
inner join books b on a.title = b.title and a.year = b.year
where b.type = 'Horror' 
group by a.authorname
having min(b.pages) > 40