I have a question which relates mainly to SQL-syntax, but as I'm not sure it can be solved purely in SQL alone and most probably need some aggregating and munching in our beloved Perl language, I post this question here.

I have a database which has several tables (say "Books" and "Authors" to keep it simple) amongst which exists a "many-to-many" relationship. To set this relationship up, I added the usual "BooksAuthors" table, which has foreign keys pointing to the "Books" and the "Authors" table.

This works nice and I can collect easily the names of the authors for a certain book or the books one author has written. This is all standard stuff as found in many tutorials on SQL. Even Class::DBI and its brethren knows how to do it.

Now in my application I want to include a search function which will find me the book(s) written by more than one author: "Give me the list of books written by Jones AND Smith". 'Jones' and 'Smith' being co-authors for these books as it were.

Of course an SQL such as SELECT book FROM BooksAuthors WHERE Author='Jones' AND Author='Smith' will not work since each row of this table only has one Author and that field cannot be 'Jones' and 'Smith' at the same time.

So I vaguely remembered something about self-joins and tried the following: SELECT book FROM BooksAuthors AS B1 JOIN BooksAuthors AS B2 WHERE B1.Author = 'Jones' AND B2.Authors = 'Smith' AND B1.Book = B2.Book

That seems to work, but gets me into problems if I want to check for three (or more) authors. It seems I will have to add more and more self-joins and I wonder if that is The Right Way. Or is there perhaps a more Perlish-way to handle this?

UPDATE: Thanks all for the answers and good suggestions. The solution suggested by Corion seems best esp. when combined with the 'IN' syntax.