My Dear Fellow PerlMonks!

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.

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

In reply to Many-to-many relationships in databases : SOLVED by CountZero

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.