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).
In Section
Seekers of Perl Wisdom