On Fri, 10 Dec 2010 12:57:48 +0100, luuk34 <luu...@gmail.com> wrote: > > >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..
That's true, but it would be bad to allow a user to rate the same movie twice. There is a reason such data wasn't included in the sample. So I assumed: CREATE TABLE userrates ( id INTEGER PRIMARY KEY NOT NULL , userid INTEGER , idtitle INTEGER , rate INTEGER , UNIQUE (userid,idtitle) ); -- ( Kees Nuyt ) c[_] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users