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

That's close but will also return books authored by only one of 'Jones' or 'Smith' alone. Of course, some quick Perl filtering might be easier than doing it in SQL.

  • Comment on Re^2: Many-to-many relationships in databases

Replies are listed 'Best First'.
Re^3: Many-to-many relationships in databases
by terce (Friar) on Oct 28, 2005 at 08:19 UTC
    I don't think that's true, unless different SQL engines implement IN differently.
    On all the engines I've used, IN is equivalent to a list of OR statements.
[reply]

      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).

[reply]
[d/l]
        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]

In Section Seekers of Perl Wisdom