How to JOIN two FKs from a table to another table?

问题:

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

Table relationship:

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

Table users:

user_id,
other user information

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

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

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.

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

回答1:

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)

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)

回答2:

To use both foreign keys, you need two joins (one for each):

select * 
  from relationship r 
 inner join users u1 on (u1.user_id = r.first_user)
 inner join users u2 on (u2.user_id = r.second_user)

回答3:

The {relationship_id, first_user, second_user} table contains a repeating group.

Just remove one of them (that makes relationship_id non-unique) and add a surrogate key (or add userid to the primary key).

CREATE TABLE relationships
( id INTEGER NOT NULL PRIMARY KEY
, relationship_id INTEGER NOT NULL
, user_id INTEGER NOT NULL REFERENCES users(user_id)
);

It would be a nice homework assignment to try to generate constraints for this construct.

Another way (for binary relations like marriages) is to put the "significant Other" link in the users table:

 CREATE TABLE lusers
 ( luser_id INTEGER NOT NULL PRIMARY KEY
 , magnificent_other INTEGER REFERENCES lusers (luser_id)
 ...
 );

As a side effect, this will also make bigamy impossible. Again: nice homework assignment to implement the constraints.