我有这样的表:
create table users (
user_id uuid,
user_name varchar(32)
);
create table messages (
message_id uuid,
from_id uuid references users(user_id),
to_id uuid references users(user_id),
the_message varchar(140),
primary key(message_id)
);
如果我执行以下操作:
select * from messages
我将获得UUID。我想显示给定uuid的users.user_name。这让我很接近:
select
users.user_name as from_id,
users.user_name as to_id
from messages
join users on users.user_id = messages.from_id
;
结果在"from"one_answers"to"字段中显示相同的用户。
如何显示from_id的user_name,以及to_id的user_name?
您需要JOIN
用户表两次——一次用于from用户,一次用于to用户:
select
messages.*,
f.user_name as from_id,
t.user_name as to_id
from messages
join users f on f.user_id = messages.from_id
join users t on t.user_id = messages.to_id
;