How can I use SQL to find all books written by the author of a certain book without using sub-querie-CodePudding

For example, if I have the following two tables:

AUTHOR

AuthorID	Name
1	James Joyce
2	Enid Blyton

BOOK

BookID	Title	authID
1	Dubliners	1
2	Famous Five	2
3	Finnegans Wake	1

Is it possible to return all books by the author of 'Dubliners' knowing only this title and without knowing the Author ID or name of the author and without the use of a sub-query? Is it possible to query this using an INNER JOIN?

So the query would ideally return:

Title
Dubliners
Finnegans Wake

CodePudding user response：

SELECT Title from BOOK INNER JOIN AUTHOR on BOOK.authID = AUTHOR.AuthorID

I think it can be like this

CodePudding user response：

We only use book table. We mark when title = 'Dubliners', then we use count() over() partitioned by authID to group all the relevant book, and then we use where cnt = 1 to choose the books we want.

select  title
from    (
        select  *
                ,count(case when title = 'Dubliners' then 1 end) over(partition by authID) as cnt
        from    book 
        ) t
where   cnt = 1

title
Dubliners
Finnegans Wake

Fiddle