我有三张桌子:
用户,
项目。
喜欢。
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';
如何发出请求
项目
属于
用户
,考虑到
现有的喜欢
属于
其他用户
'US789'
?
Worked example
我需要结果:
item_id owner_id its_like
1 it123 us123 us789
2 it456 us123
3 it789 us123 us789
谢谢您。