php - 外键链接在查询中两次引用同一个表

Question

我需要一些帮助来用 PHP 编写 mysql 语句。我在同一个表中引用了两次相同的外键，我需要使用 SELECT 语句引用这两个实例。 IE。我有一个名为消息的表，然后是标有发件人和收件人的两列。两列都链接到表 users。我需要在同一个查询中从发送者用户和接收者用户中提取信息。我在 StackOverflow 上看到了其他答案，但我似乎没有正确地做到这一点:/

这是我当前在 PHP 中的 mysqli 调用，仅获取发件人用户链接:

require_once('includes/connection.inc.php');

$db = dbConnect();

$stmt = $db->stmt_init();

$stmt = $db->prepare("
SELECT message.timestamp, message_type.type, 
message.sender, message.receiver, message.sender_language, 
language.translate_code, message.subject_translate, message.body_translate,
message.subject, message.body, users.image, users.externalid, users.fname,
users.lname FROM message 
INNER JOIN users ON message.sender=users.id 
INNER JOIN language ON message.sender_language=language.id
INNER JOIN message_type ON message.type=message_type.id 
WHERE (message.receiver=? AND message.archived='n') 
ORDER BY message.timestamp DESC 
LIMIT 0,10 ");

$stmt->bind_param('i', $session_id);

$stmt->execute();

$stmt->bind_result($row['date'], $row['message_type'], $row['sender'],
$row['receiver'], $row['sender_language'], $row['sender_language_code'],
$row['subject'], $row['body'], $row['subject_original'], 
$row['body_original'], $row['image'], $row['user_name'], $row['fname'],
$row['lname'] );

$record_count = 0;

while($stmt->fetch()){...

这段代码工作正常，但我需要添加

"INNER JOIN users ON message.receiver=users.id"

这样我就可以在 message.receiver 而不仅仅是 message.sender 中包含“users.image, users.externalid, users.fname, users.lname”

我正在引用这个问题/答案重写我的代码，但后来卡住了:ForeignKey Referencing Same Table

Answer 1

使用别名尝试此策略:

SELECT msg.id, sender.id, receiver.id
FROM message msg
INNER JOIN users sender ON msg.sender=sender.id 
INNER JOIN users receiver ON msg.receiver=receiver.id 
WHERE [PUT_THE_WHERE_CONDITIONS_HERE]
ORDER BY message.timestamp DESC 
LIMIT 0,10