On 10-12-10 12:03, luuk34 wrote: > > > On 10-12-10 11:53, lucavb 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? >> >> Thanks in advance, >> >> Luca > > untested, something like: > > SELECT a.id, a.userid, a.idtitle, a.rate > FROM userrates a > INNER JOIN userrates b ON a.userid=b.userid AND b.idtitle=10 > WHERE a.idtitle=1 > >
oops... SELECT a.id, a.userid, a.idtitle, a.rate FROM userrates a INNER JOIN userrates b ON a.userid=b.userid AND b.idtitle=10 WHERE a.idtitle=1 UNION SELECT a.id, a.userid, a.idtitle, a.rate FROM userrates a INNER JOIN userrates b ON a.userid=b.userid AND b.idtitle=1 WHERE a.idtitle=10 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
