Many-to-many relationships in databases : SOLVEDby CountZero (Bishop)
|on Oct 28, 2005 at 06:18 UTC||Need Help??|
CountZero has asked for the wisdom of the Perl Monks concerning the following question:
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?
"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law