在sql中从另一个表中的数据中选择3个用户的最佳方法是什么？ [关闭]

Question

So I am currently working on a site that contains a loan application, in that loan application there is a primary borrower and then there can be up to 2 co-borrowers. What I've done is created a loanSubmission table that contains a uid1, uid2 and uid3 column which represents the user id and then the number of the user. The id of the user is stored in each one of these columns and the users full information is stored within the users table.

I need to select three users from the data in another table. Is there a way to achieve this using a join or would I have to use sub queries? I've never had to join multiple users based on info from another table so any direction would be greatly appreciated!

Answer 1

It's pretty simple, you use aliases to join to the same table multiple times:

SELECT user1.id, user2.id, user3.id FROM loanSubmission
  INNER JOIN users user1 ON user1.id = loanSubmission.uid1
  INNER JOIN users user2 ON user2.id = loanSubmission.uid2
  INNER JOIN users user3 ON user3.id = loanSubmission.uid3

or depending on if you want blank users(which you probably do)

SELECT user1.id, user2.id, user3.id FROM loanSubmission
  LEFT OUTER JOIN users user1 ON user1.id = loanSubmission.uid1
  LEFT OUTER JOIN users user2 ON user2.id = loanSubmission.uid2
  LEFT OUTER JOIN users user3 ON user3.id = loanSubmission.uid3