同一张表的两个外键.我如何编写这个 SELECT 语句? [英] Two foreign keys of the same table. How do I write this SELECT statement?

问题描述

user_id    username    thumb_id    fullimage_id
1          jprescott   14          15
2          lpausch     18          19

图像表

image_id    path
14          jprescott/small.jpg
15          jprescott/big.jpg
16          msamuels/small.jpg
17          msamuels/big.jpg
18          lpausch/small.jpg
19          lpausch/big.jpg

现在，我如何编写 SELECT 语句来检索具有拇指和全图路径的用户?问题是同一个表有两个外键.

Now, how do I write a SELECT statement to retrieve a user with the thumb and fullimage paths? The issue is that of having two foreign keys of the same table.

推荐答案

你做了两个连接:

SELECT u.username, i1.path AS thumb, i2.path AS full
  FROM users AS u
    JOIN images AS i1 ON u.thumb_id = i1.image_id
    JOIN images AS i2 ON u.fullimage_id = i2.image_id

这篇关于同一张表的两个外键.我如何编写这个 SELECT 语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！