×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

comparing arbitrary sets

comparing arbitrary sets

comparing arbitrary sets

(OP)
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

RE: comparing arbitrary sets

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

CODE

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

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

RE: comparing arbitrary sets

(OP)
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!

RE: comparing arbitrary sets

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

smile

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

RE: comparing arbitrary sets

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close