Re: Many-to-many relationships in databases

I'm not the strongest on SQL, but I think you can use 'in' to solve your problem i.e.

SELECT book FROM BooksAuthors WHERE Author in ('Jones', 'Smith');
[download]

SQL syntax may not be perfect - not my main field of expertise

Also look at SQL::Abstract and the extension to Class::DBI that uses it, Class::DBI::AbstractSearch, for an easy way to build these queries dynamically using perl data structures, for any length list of authors.

...reality must take precedence over public relations, for nature cannot be fooled. - R P Feynmann

Comment on Re: Many-to-many relationships in databases Download Code

Replies are listed 'Best First'.
Re^2: Many-to-many relationships in databases by Corion (Patriarch) on Oct 28, 2005 at 06:37 UTC
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.	[reply]
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]
Re^4: Many-to-many relationships in databases by Corion (Patriarch) on Oct 28, 2005 at 08:25 UTC
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]
Re^5: Many-to-many relationships in databases by terce (Friar) on Oct 28, 2005 at 08:38 UTC
Re^6: Many-to-many relationships in databases by Corion (Patriarch) on Oct 28, 2005 at 08:49 UTC
Some notes below your chosen depth have not been shown here


Perl-Sensitive Sunglasses
	PerlMonks