Say I have a database for storing info on books, and I want to search by book title or author.

Books can be by multiple authors, so this is a many-many relationship.

I have normalised my tables and ended up with this:

Books(book_ID(pk), book_name)
book_authors(book_ID(pk), author_id(pk))
authors(author_id(pk), author_name)

I have in the database two authors, say "Mr Smith" and "Mr Jones", and 3 books, "Book A", "Book B" and "Book C"

"Book A" is written by Mr Smith
"Book B" is written by Mr Smith and Mr Jones
"Book C" is written by Mr Jones

How can I now search for a book that is written by BOTH Mr Jones AND Mr Smith?

It should return just Book B.

It can be done with a nested SELECT Query in SQL, but then what if there are three authors to a book, I'd have to add another layer of nesting.

Is there a more efficient way to do this?

It looks to me like this is an intersect.

Unfortunately there aren't just one kind of many-to-many relationships, so there isn't a "one size fits all" solution for this kind of problem. If you're using the classic many-to-many relationship, then the solution is easier...

Write a SELECT statement that will retrieve all of the appropriate rows for this many-to-many relationship. Use the WHERE clause to filter out only the author(s) of interest. Count the returned rows, and compare that count (two in this case) against the optimal count (two, one each for Jones and Smith). If the counts are equal, you've got a "full solution".

One thing that you need to be careful to decide what constitutes a "correct" answer for in you case is over counts. For instance, if you expect two, but get four (because Mrs Smith and Mrs Jones were co-authors too), how does that affect your answer?

-PatP

Could you elaborate on this a little, please?

Sure. Check out Brett Kaiser's great post on how to get answers quickly via forums. Post the basic information needed, and I'll cook you up an example instead of just explaining more about the abstract idea of this solution.

-PatP