CODEWITHSUNDEEP
sqlpostgresql
user3001
user3001

Reputation: 3487

SQL: Getting implicit ratings as a user-item list

I have the following tables

I want to select the fields user.id, item.id and rating (boolean field)such that for every user and every item rating is true iif there exists an entry in the positiverating table and otherwise false?

Some thoughts:

Assuming that there are 100000 items and the same amount of users, the resulting query needs to be quite efficiently. I suspect some left/right joins are needed, but I feel a bit stuck.

To be more specific, I need a query for PostgreSQL.

Upvotes: 0

Views: 72

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270201

Is this what you are looking for?

select u.userid, i.itemid, (pr.itemid is not null)
from user u cross join
     item i left outer join
     positiverating pr
     on pr.userid = u.userid and pr.itemid = i.itemid;

This assumes no duplicate user/item combinations in the positiverating table.

If you have duplicates in positiverating, you could use a group by. However, I would recommend using exists instead. I think the following will work:

select u.userid, i.itemid,
       exists (select 1 from postivierating where pr.userid = u.userid and pr.itemid = i.itemid)
from user u cross join
     item i;

Upvotes: 1

Related Questions