in reply to Re^3: Many-to-many relationships in databases
in thread Many-to-many relationships in databases : SOLVED

How does that contradict my statement? The BookAuthors table seems to contain one entry for each pair (book, author), so (in a contrived example) it could look like the following:

book       author
--------------------
1          Jones
1          Miller
2          Jones
2          Smith
3          Smith
4          Jones

[download]

The statement will return every book id for the given query of author IN ('Jones', 'Smith'), because, as you say, IN is more or less equivalent to a series of OR statements (I'm not sure about differences regarding NULL values).

Replies are listed 'Best First'.
Re^5: Many-to-many relationships in databases
by terce (Friar) on Oct 28, 2005 at 08:38 UTC
    Perhaps I've misunderstood the OP, or your reply, but I don't understand why you say that using IN is only 'close' to correct - I don't see why it won't provide the results that the OP is after, for any number of authors.
[reply]

      Quoting from the original post:

      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.

      Using IN on my example table will return lots of books that were not written by both, 'Smith' and 'Jones'. It will return all books co-authored by the two, but it will also return books written by only one of them.

[reply]
        Fair enough - my misunderstanding
[reply]

In Section Seekers of Perl Wisdom