CODEWITHSUNDEEP
postgresql
Sergey Kozlov
Sergey Kozlov

Reputation: 452

Postgresql existing likes of another user

I have three tables: Users, Items. Likes.

CREATE TABLE t_users (
user_id varchar PRIMARY KEY,
name varchar
);

CREATE TABLE t_items (
item_id varchar PRIMARY KEY,
owner_id varchar
);

CREATE TABLE t_likes (
like_id varchar PRIMARY KEY,
item_id varchar references t_items(item_id),
user_id varchar references t_users(user_id)
);

INSERT INTO t_users VALUES ('us123', 'us123');
INSERT INTO t_users VALUES ('us456', 'us456');
INSERT INTO t_users VALUES ('us789', 'us789');

INSERT INTO t_items VALUES ('it123', 'us123');
INSERT INTO t_items VALUES ('it456', 'us123');
INSERT INTO t_items VALUES ('it789', 'us123');
INSERT INTO t_items VALUES ('it987', 'us456');
INSERT INTO t_items VALUES ('it654', 'us456');
INSERT INTO t_items VALUES ('it321', 'us456');

INSERT INTO t_likes VALUES ('lk123', 'it123', 'us789');
INSERT INTO t_likes VALUES ('lk456', 'it456', 'us123');
INSERT INTO t_likes VALUES ('lk789', 'it789', 'us789');
INSERT INTO t_likes VALUES ('lk987', 'it987', 'us456');
INSERT INTO t_likes VALUES ('lk654', 'it654', 'us789');
INSERT INTO t_likes VALUES ('lk321', 'it321', 'us789');

select * from t_items where owner_id = 'us123';

How to make a request that will give out the items belonging to the user, taking into account the existing likes of another user 'us789' ?

Worked example

I need result:

   item_id  owner_id  its_like
1   it123   us123   us789
2   it456   us123
3   it789   us123   us789

Thank you.

Upvotes: 3

Views: 36

Answers (1)

klin
klin

Reputation: 121784

Use left join. Place the other user id in the join condition:

select i.item_id, i.owner_id, l.user_id as its_like
from t_items i
left join t_likes l
on i.item_id = l.item_id and l.user_id = 'us789'
where owner_id = 'us123';

 item_id | owner_id | its_like 
---------+----------+----------
 it123   | us123    | us789
 it456   | us123    | 
 it789   | us123    | us789
(3 rows)

Upvotes: 1

Related Questions