sql - 在 Postgres 中加入 4 个表

Question

我有四个主要的表：

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

CREATE TABLE t_items (
item_id varchar PRIMARY KEY,
owner_id varchar not null references t_users(user_id),
title varchar
);
CREATE TABLE t_access_items_users (
access_iu_id varchar PRIMARY KEY,
item_id varchar not null references t_items(item_id),
user_id varchar not null references t_users(user_id)
);

CREATE TABLE t_friends (
friend_id varchar PRIMARY KEY,
from_user_id varchar not null references t_users(user_id),
to_user_id varchar not null references t_users(user_id)
);

有数据：

INSERT INTO t_users VALUES ('us123', '[e-mail]');
INSERT INTO t_users VALUES ('us456', '[e-mail]');
INSERT INTO t_users VALUES ('us789', '[e-mail]');
INSERT INTO t_users VALUES ('public', '[e-mail]');

INSERT INTO t_items VALUES ('it123', 'us123', 'title1');
INSERT INTO t_items VALUES ('it456', 'us456', 'title2');
INSERT INTO t_items VALUES ('it678', 'us789', 'title3');
INSERT INTO t_items VALUES ('it323', 'us123', 'title4');
INSERT INTO t_items VALUES ('it764', 'us456', 'title5');
INSERT INTO t_items VALUES ('it826', 'us789', 'title6');
INSERT INTO t_items VALUES ('it568', 'us123', 'title7');
INSERT INTO t_items VALUES ('it038', 'us456', 'title8');
INSERT INTO t_items VALUES ('it728', 'us789', 'title9');

INSERT INTO t_access_items_users VALUES ('aiu123', 'it123', 'us123');
INSERT INTO t_access_items_users VALUES ('aiu456', 'it456', 'us456');
INSERT INTO t_access_items_users VALUES ('aiu678', 'it678', 'us789');
INSERT INTO t_access_items_users VALUES ('aiu323', 'it323', 'us123');
INSERT INTO t_access_items_users VALUES ('aiu764', 'it764', 'us456');
INSERT INTO t_access_items_users VALUES ('aiu826', 'it826', 'us789');
INSERT INTO t_access_items_users VALUES ('aiu568', 'it568', 'us123');
INSERT INTO t_access_items_users VALUES ('aiu038', 'it038', 'us456');
INSERT INTO t_access_items_users VALUES ('aiu728', 'it728', 'us789');
INSERT INTO t_access_items_users VALUES ('aiu728', 'it728', 'us789');
INSERT INTO t_access_items_users VALUES ('apu123', 'it678', 'public');
INSERT INTO t_access_items_users VALUES ('apu222', 'it123', 'public');
INSERT INTO t_access_items_users VALUES ('apu111', 'it456', 'public');
INSERT INTO t_access_items_users VALUES ('aiu333', 'it728', 'public');
INSERT INTO t_access_items_users VALUES ('aiu444', 'it826', 'public');

INSERT INTO t_friends VALUES ('f123', 'us123', 'us456');

请求类型：

select *
from t_access_items_users
inner join t_items on t_access_items_users.item_id = t_items.item_id
inner join t_users on t_access_items_users.user_id = t_users.user_id
where t_access_items_users.user_id = 'public';

返回 5 行。

为什么查询类型：

select 
t_items.item_id,
t_items.owner_id,
t_items.title
from t_access_items_users
inner join t_items on t_access_items_users.item_id = t_items.item_id
inner join t_friends on t_items.owner_id = t_friends.from_user_id
where t_friends.to_user_id = 'us456'
or t_access_items_users.user_id = 'public';

返回 4 行？如何提出正确的请求以获取所有需要的行？最终，我想获取这些项目可以访问公众以及用户朋友的友谊的数据。如何进行返回所有元素的查询：

t_friends.to_user_id = 'us456'
or t_access_items_users.user_id = 'public'

谢谢你。

标签： sqlpostgresql