CODEWITHSUNDEEP
sqloracle-database
Sha
Sha

Reputation: 125

SQL, finding other entries based on them having one attribute in common the same

Please pardon my bad English. I have a table which looks like the below:

Book(BookID, Title)

Author(BookID, AuthID)

And there's a certain book, say it's ID 52. I need to find the other books by the authors who wrote the book with the ID of 52, but not include ID 52 in the list of IDs that I output.

So I assume that I can JOIN the two tables, but the problem is that I can't use a sub-query, so I'm stuck here. I have a feeling that I'm supposed to be using WHERE, but I'm not sure how to do it as thus far all I can figure out how to get is a list of the authors who are assigned to the book with WHERE Author.BookID = 52.

select Book.BookID
from Book
join Author
on Book.BookID=Author.BookID

Is there a way to get the AuthID of those who are assigned to the book with ID 52 and then output the other books they are assigned to? And then MINUS the ID 52 book away? Or am I going about this the wrong way?

Again, any help or point in the right direction would be much appreciated.

Upvotes: 0

Views: 95

Answers (1)

Dirk Huber
Dirk Huber

Reputation: 912

You want to find all other books of the same author as the author of a given BookId, right?

SELECT b.BookId 
FROM   Author a 
       INNER JOIN Author b ON a.AuthID = b.AuthID AND a.BookId <> b.BookId
WHERE  a.BookId = 52;

Upvotes: 4

Related Questions