CODEWITHSUNDEEP
mysqlsqldatabase
Pritesh Jain
Pritesh Jain

Reputation: 9146

query to find diff between two records

I have following tables

movies
  - id

details
  - user_id 
  - movie_id 
  - rating

users
  - id

detail belongs to user and movie

I want to find the the diff between the rating of two users say id 3,10

simply I want answer of this

sum(10-(user1.rating - user2.rating))
   where rating is > 0

i.e both users should have given at-least non zero rating

Upvotes: 1

Views: 119

Answers (4)

Gábor Plesz
Gábor Plesz

Reputation: 1223

Update: I replace the syntax from MSSQL to MySql

You can check this on SQL Fiddle:

create table movies (id int);
insert into movies (id) values (1);
insert into movies (id) values (2);
insert into movies (id) values (3);
insert into movies (id) values (4);
insert into movies (id) values (5);

create table users (id int);
insert into users (id) values (1);
insert into users (id) values (2);
insert into users (id) values (3);
insert into users (id) values (4);
insert into users (id) values (5);
insert into users (id) values (10);

create table details (user_id int, movie_id int, rating int);

insert into details (user_id, movie_id, rating) values (3,1,1);
insert into details (user_id, movie_id, rating) values (3,2,2);
insert into details (user_id, movie_id, rating) values (3,3,3);
insert into details (user_id, movie_id, rating) values (3,4,1);
insert into details (user_id, movie_id, rating) values (3,5,4);

insert into details (user_id, movie_id, rating) values (10,1,3);
insert into details (user_id, movie_id, rating) values (10,2,1);
insert into details (user_id, movie_id, rating) values (10,3,2);
insert into details (user_id, movie_id, rating) values (10,4,1);
insert into details (user_id, movie_id, rating) values (10,5,5);

and

select 
  sum(10-(details1.rating - details2.rating))
from
  movies
  inner join details details1 on movies.id = details1.movie_id
  inner join users user1 on details1.user_id = user1.id 
  inner join details details2 on movies.id = details2.movie_id
  inner join users user2 on details2.user_id = user2.id 
where 
  (user1.id=3) 
  and
  (user2.id=10)
  and
  (details1.rating is not null) and (details1.rating > 0)
  and
  (details2.rating is not null) and (details2.rating > 0)

Upvotes: 0

fancyPants
fancyPants

Reputation: 51928

select 
d1.movie_id
, d1.rating as user1Rating
, d2.rating as user2Rating
, abs(d1.rating - d2.rating)
from
details d1
inner join details d2 on d1.movie_id = d2.movie_id
where d1.user_id = 1
and d2.user_id = 2

See it working live in an sqlfiddle.

Upvotes: 2

Janne Matikainen
Janne Matikainen

Reputation: 5121

CREATE TABLE ratings
(   userId int, movieId int, rating int)

INSERT INTO ratings (userId, movieId, rating) VALUES
    (3, 1, 5), (10, 1, 8),
    (3, 2, 10), (10, 2, 3)

SELECT r1.movieId, (r1.rating - r2.rating) FROM ratings as r1
INNER JOIN ratings as r2 on r1.movieId = r2.movieId
WHERE r1.userId = 3 and r2.userId = 10

Upvotes: 2

pieterlouw
pieterlouw

Reputation: 81

DECLARE @UserRating1 int
DECLARE @UserRating2 int
DECLARE @UserID1 int
DECLARE @UserID2 int
DECLARE @MovieID int

SET @MovieID = 99
SET @UserID1 = 3
SET @UserID2 = 10

SET @UserRating1 = (SELECT Rating FROM details WHERE movie_id = @MovieID AND user_id = @UserID1 AND Rating > 0)
SET @UserRating2 = (SELECT Rating FROM details WHERE movie_id = @MovieID AND user_id = @UserID2  AND Rating > 0)

PRINT @UserRating1 - @UserRating2

Upvotes: 0

Related Questions