Hi,

I'm having the following problem. I have a table that contains
information about books people read, i.e. each row has two columns,
people id and book id.
I need to do the following query: retrieve the list of all people that
read ALL the books in a given list, e.g. all the people that read book
X and book Y and book Z.
Tu further complicate the situation, an extended query also include an
exclusion crieteria, i.e. a list of books that the user should not
have read.

I really have no clue on how to approach this.
Any help will be much appricated.

Abe.

abraham.pinsker@gmail.com wrote:
> I'm having the following problem. I have a table that contains
> information about books people read, i.e. each row has two columns,
> people id and book id.
> I need to do the following query: retrieve the list of all people that
> read ALL the books in a given list, e.g. all the people that read book
> X and book Y and book Z.

This part you could do with "book IN('','','')" for the books and the "GROUP
BY user" and have a COUNT(*) and only select those with a count of 3.

SELECT COUNT(*) AS Num, User FROM table WHERE Book IN('id1','id2','id3') GROUP
BY User HAVING Num=3;


> Tu further complicate the situation, an extended query also include an
> exclusion crieteria, i.e. a list of books that the user should not
> have read.

Here is a slow query (sure someone else will have a far better one, and
require that you have a recent enough mysql server that supports subqueries)

SELECT COUNT(*) AS Num, User FROM table WHERE Book IN('id1','id2','id3') AND
User NOT IN(SELECT User FROM table WHERE Book='id4') GROUP BY User HAVING Num=3;



--

//Aho