comparing arbitrary sets

[coolgeek1987]

I've got a user table and a book table, with a fact table recording the books read by each user. For simplicity, the tables and their fields are:

Table Fields
----- ------
user user_id
book book_id
books_read user_id, book_id

I have a web app that lets a user select a list of books from a dropdown menu, resulting in a string that looks like "book_id1, book_id2, book_id3..."

I want my app to search the books_read table to present the user_id of all users who read the entire set of books in that string.

Is there an elegant way to do this in SQL, or do I have to parse the string and build my SQL statement on the fly along the lines of:

select user_id
from books_read
where book_id = book_id1
intersect
select user_id
from books_read
where book_id = book_id2
intersect
select user_id
from books_read
where book_id = book_id3
...

thanks

 

[r937]

assuming that the values in the string returned from the dropdown selection are distinct (and why wouldn't they be), you can do this --

SELECT user_id 
  FROM books_read
 WHERE book_id IN ( [i]book_id1, book_id2, ...[/i] )
GROUP
    BY user_id
HAVING COUNT(*) = [i]n[/i] /* number of ids in the list /*

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[coolgeek1987]

r937 -

That seems to do the trick. Thanks a lot - especially since I just found out that MySQL doesn't seem to support intersect.

I'm going to have to check out your book!

 

[r937]

thanks

and if you have any further questions, the ANSI SQL forum might not be the best place for them, as mysql syntax varies considerably in several places



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[coolgeek1987]

ah, just found the MySQL forum... I'm usually pretty good on my own - I've been developing on Oracle since 7.3 - but I'm much more of a general programmer than a SQL guru.

Thanks again