Works great! Thanks! 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; > -- > ( Kees Nuyt > ) > c[_] > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >
-- View this message in context: http://old.nabble.com/Select-tp30425149p30432694.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
