the way to do this with a static SQL statement is to have a table with the list of books in the selection and
the required ranking for the book
ie
booksetrequirements
setid int
bookid int
rating int
select distinct BR.Userid
from BooksRead as BR
Where exists (select setid from booksetrequirements as bsr
where br.bookid=bsr.bookid
and setid = ????
and br.rating >= bsr.rating)
and not exists (select setid from booksetrequirements as bsr
left outer join booksread as x
on x.bookid=bsr.bookid
and x.rating < bsr.rating
and x.userid=br.userid
where setid = ????
and x.bookid is null
)
Main Topics
Browse All Topics
by: Raynard7Posted on 2007-02-18 at 16:10:01ID: 18560350
You could do
sect distinct br.userID from BooksRead as br where (select count(*) from BooksRead as br1 where br1.userId = br.userID and br1.bookID in (2,8,12,35,36) and br1.Rating >= 4) >= 5
where it says find the count of rows for the given user - in the list of required books and scored >= 4 and make a total of 5 rows (for each book)
so to change the query dynamically if you change
>= 4 for the desired rating
>=5 the nubmer of books to meet the criteria
in (2,8,12,35,36) for the books you want to measure.