Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

Re: Many-to-many relationships in databases

by leriksen (Curate)
on Oct 28, 2005 at 06:32 UTC ( #503571=note: print w/replies, xml ) Need Help??

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

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');

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

Replies are listed 'Best First'.
Re^2: Many-to-many relationships in databases
by Corion (Pope) 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.

      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.

        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

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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://503571]
[Corion]: Meh. I need to find myself a better "programming" feed than r/programming (which is just HackerNews reposts and advertisements, very little code). Maybe I should select a list of links that I liked on r/programming and then google for ...
[Corion]: ... an aggregator site that also listed (ideally) all of these links once. And maybe also have an exclude list to blacklist some of the most spammy links that the site may have never mentioned at all
[marto]: I gave up reading HN at all, and feel much better for it :P
[Corion]: Now, how to best automate that Google search ... :-)
[Corion]: marto: Yeah, I'm also short of not reading it at all. I already stopped reading it in the morning because it gave me a foul mood.
[Corion]: The good posts on HN are the non-computing posts. Most of the other stuff is maybe relevant to you if you are 20 and live in Silicon Valley...
[Corion]: Two attributes that don't describe me.
[Corion]: Maybe I should also write a curator for HN or simply not read it anymore, like you do.

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (9)
As of 2017-07-24 13:22 GMT
Find Nodes?
    Voting Booth?
    I came, I saw, I ...

    Results (354 votes). Check out past polls.