CODEWITHSUNDEEP
sql
Xeon
Xeon

Reputation: 5989

Select books having specified authors

I have Book and Author tables. Book have many Authors (I know that it should be many-to-many it's just for the sake of this example).

How do I select all books that have been written by authors: X and by Y in one sql query?

EDIT

Number of authors can be variable - 3, 5 or more authors.

I can't figure it out now (I've tried to do JOINs and sub-queries).

SELECT * FROM book ...?

Upvotes: 0

Views: 11739

Answers (4)

Arwin van Arum
Arwin van Arum

Reputation: 19

I am assuming that your Author table has a Book ID (as this would give many author to one book).

SELECT * from Books Where ID IN
(SELECT BookID from Author A 
 JOIN Author B on A.BookID = B.BookID and A.ID <> B.ID 
 WHERE A.ID IN ('X', 'Y') AND B.ID IN ('X','Y'))

EDIT: Hamlet's answer is much better (scaleable).

Upvotes: 0

Corbin
Corbin

Reputation: 33437

You can just double join the authors table.

SELECT Book.* from Book 
    JOIN Author author1 
        ON author1.book_id = Book.id AND author1.author_name = 'Some Name'
    JOIN Author author2
        ON author2.book_id = Book.id AND author1.author_name = 'Some Other Name'
GROUP BY Book.id

The JOINs ensure that only books with Both authors are returned, and the GROUP BY just makes the result set only contain unique entries.

It's worth noting by the way that this query will bring back books that have at least the two authors specified. For example, if you want books only by Smith and Jones, and not by Smith, Jones and Martin, this query will not do that. This query will pull back books that have at least Smith and Jones.

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

SELECT
  B.Name
FROM Books B
  JOIN Authors A
    ON B.AuthorID = A.ID
WHERE A.Name IN ('X', 'Y')
GROUP BY B.Name
HAVING COUNT(DISTINCT A.ID) = 2

Upvotes: 4

tony b
tony b

Reputation: 1351

select * from book where author=.... ?

I found it hard to understand your structure but this seems like the best way?

Hope this helps

Upvotes: 0

Related Questions