On Fri, 10 Dec 2010 02:53:32 -0800 (PST), lucavb
<[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
