On Fri, 10 Dec 2010 12:57:48 +0100, luuk34 <[email protected]> wrote:
>
>
>On 10-12-10 12:43, Kees Nuyt wrote:
>> 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;
>
> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
