On 10-12-10 12:43, Kees Nuyt wrote: > On Fri, 10 Dec 2010 02:53:32 -0800 (PST), lucavb > <bombhere...@gmail.com> wrote: > >> Hello, >> i need to to do this: >> >> i have this table: "userrates", composed by: id, userid, idtitle, rate. >> >> for example: >> (1, 12, 1, 3) >> (2, 15, 99, 4) >> (3, 22, 10, 1) >> (10, 22, 1, 5) >> (5, 166, 37, 1) >> (4, 244, 10, 2) >> (6, 298, 1, 4) >> (7, 298, 10, 3) >> (8, 298, 15, 2) >> >> i need to extract only the rows with the userid >> who had voted both fims (1 and 10): >> >> the result will be: >> (3, 22, 10, 1) >> (10, 22, 1, 5) >> (6, 298, 1, 4) >> (7, 298, 10, 3) >> >> How can i do that? >> > Assuming you mean idtitle where you write "fims": > > SELECT a.id, a.userid, a.idtitle, a.rate > FROM userrates AS a > INNER JOIN ( > SELECT userid > FROM userrates > WHERE idtitle IN (1,10) > GROUP BY userid > HAVING count(id) = 2 > ) AS b ON b.userid = a.userid > WHERE a.idtitle IN (1,10) > ORDER BY a.userid,a.id;
A user who has rated movie 1 twice and has not rated movie 10, would show up in your result.. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
