如何将两个FK从一个表联接到另一个表? [英] How to JOIN two FKs from a table to another table?

问题描述

我有一个多对多关系表:

I have a table of many-to-many relationships as:

表relationship:

relationship_id,
first_user REFERENCES users(user_id),
second_user REFERENCES users(user_id),

表users:

user_id,
other user information

要读取给定用户的朋友(可以在first_user或second_user中)，我需要联接两个表(relationships和users)，但是表relationships的两列是FK

To read friends of a given user (which can be in first_user or second_user), I need to JOIN two tables (relationships and users), but two columns of the table relationships are FK

问题1:我如何联接两个表以同时使用两个FK?

Question 1: How can I JOIN two tables to use both FKs?

我有一个待处理请求的表，与表relationships相似.批准请求后，该请求将从requests中删除，并插入到relationships中.

I have a table for pending requests which is similar to the table relationships. Upon approving a request, it will be deleted from requests and INSERTed into relationships.

问题2:如何在一个查询中联接三个表来检索连接和挂起的请求.

Question 2: How can I JOIN three tables to retrieve connection and pending requests in one query.

推荐答案

您将必须为用户"表的实例提供别名，以便您可以分别引用它们:

You will have to give the instances of the "users" table aliases, so you can refer to them serparately:

SELECT u1.*, u2.* FROM relationship r
  JOIN users u1 ON (u1.user_id = r.first_user)
  JOIN users u2 ON (u2.user_id = r.second_user)

要从请求和关系中选择，可以使用 UNION :

To select both from requests and relationships, you can use a UNION:

SELECT u1.*, u2.* FROM relationship r
  JOIN users u1 ON (u1.user_id = r.first_user)
  JOIN users u2 ON (u2.user_id = r.second_user)
UNION
SELECT u1.*, u2.* FROM requests r
  JOIN users u1 ON (u1.user_id = r.first_user)
  JOIN users u2 ON (u2.user_id = r.second_user)

这篇关于如何将两个FK从一个表联接到另一个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！