Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Re^2: Many-to-many relationships in databases

by !1 (Hermit)
on Oct 28, 2005 at 20:31 UTC ( #503759=note: print w/replies, xml ) Need Help??


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

Well, you can fix it by using except on a query that gives all the book_id's where the author isn't one of 'Smith' or 'Jones'.

select book_id from authors where author_name in ('Smith','Jones') group by book_id having count(*)=2 except select book_id from authors where author_name not in ('Smith','Jones');

You could also alter the where clause to make certain book_id doesn't have a different author:

select book_id from authors where author_name in ('Smith','Jones') and book_id not in ( select book_id from authors where author_name not in ('Smith','Jones') ) group by book_id having count(*)=2

By the way, if anyone wants to read some of Joe Celko's old articles from DBMS magazine, here are some that are available from their website (the puzzles are really fun).

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://503759]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (5)
As of 2021-08-03 13:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My primary motivation for participating at PerlMonks is: (Choices in context)








    Results (39 votes). Check out past polls.

    Notices?