Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Posts
    5

    Searching a many-to-many architecture

    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?

  2. #2
    Join Date
    Feb 2005
    Location
    Colorado Springs
    Posts
    222
    It looks to me like this is an intersect.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  4. #4
    Join Date
    May 2003
    Posts
    5
    Quote Originally Posted by Pat Phelan
    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".
    Could you elaborate on this a little, please?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •