php MySQL 选择 JOIN 3 个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1204217/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Select JOIN 3 Tables
提问by Dodinas
I have three basic tables:
我有三个基本表：
tblUsers:
usrID usrFirst usrLast
1 John Smith
2 Bill Jones
3 Jane Johnson
pm_data:
id date_sent title sender_id thread_id content
2 2009-07-29 18:46:13 Subject 1 1 111 Message 2!
3 2009-07-29 18:47:21 Another Subject 1 222 Message 3!
pm_info:
id thread_id receiver_id is_read
1 111 2 0
2 111 3 0
3 222 2 0
4 222 3 0
Essentially, what I am trying to do is create an inbox.
本质上，我想做的是创建一个收件箱。
So, if usrID 2 (Bill Jones) opens his inbox, he will see that he 2 unread (hence the 'is_read' column) messages (threads #111 and #222).
因此，如果 usrID 2 (Bill Jones) 打开他的收件箱，他将看到 2 条未读（因此是“is_read”列）消息（线程 #111 和 #222）。
Basically, I need to know how to set up my SELECT statement to JOIN all three tables (the relationship between pm_data and pm_info brings about the message info, while the relationship between tblUsers and pm_data brings about the 'display name' of the sender), to show the most recent (by timestamp?) thread on top.
基本上，我需要知道如何设置我的 SELECT 语句来 JOIN 所有三个表（pm_data 和 pm_info 之间的关系带来了消息信息，而 tblUsers 和 pm_data 之间的关系带来了发件人的“显示名称”），在顶部显示最新的（按时间戳？）线程。
Thus, we would see something like this:
因此，我们会看到这样的事情：
<?php $usrID = 2; ?>
<table id="messages">
<tr id="id-2">
<td>
<span>
From: John Smith
</span>
<span>2009-07-29 18:47:21</span>
</td>
<td>
<div>Another subject</div>
</td></tr>
<tr id="id-1">
<td>
<span>
From: John Smith
</span>
<span>2009-07-29 18:46:13</span>
</td>
<td>
<div>Subject 1</div>
</td></tr>
</table>
Hopefully this makes sense! Thanks for any help!
希望这是有道理的！谢谢你的帮助！
EDIT: Here's my final answer:
编辑：这是我的最终答案：
I took lc's advice, and made the relationship between the two tables based on id (added a column called 'message_id' to pm_info).
我接受了 lc 的建议，并根据 id 建立了两个表之间的关系（在 pm_info 中添加了一个名为“message_id”的列）。
Then, tweaked the MySQL statement around a little bit to come up with this:
然后，稍微调整一下 MySQL 语句以得出以下结论：
SELECT pm_info.is_read, sender.usrFirst as sender_name,
pm_data.date_sent, pm_data.title, pm_data.thread_id
FROM pm_info
INNER JOIN pm_data ON pm_info.message_id = pm_data.id
INNER JOIN tblUsers AS sender ON pm_data.sender_id = sender.usrID
WHERE pm_data.date_sent IN(SELECT MAX(date_sent) FROM pm_data WHERE pm_info.message_id = pm_data.id GROUP BY thread_id) AND pm_info.receiver_id = '$usrID' ORDER BY date_sent DESC
This seems to work for me (so far).
这似乎对我有用（到目前为止）。
回答by lc.
You'll need two joins. Something like the following should get you started (although I don't 100% understand the relationship between pm_dataand pm_info):
你需要两个连接。像下面这样的东西应该让你开始（虽然我不100％理解之间的关系pm_data和pm_info）：
SELECT pm_info.is_read, sender.usrFirst + ' ' + sender.usrLast as sender_name,
pm_data.date_sent, pm_data.title, pm_data.thread_id
FROM pm_info
INNER JOIN pm_data ON pm_info.thread_id = pm_data.thread_id
INNER JOIN tblUsers AS sender ON pm_data.sender_id = tblUsers.usrID
WHERE pm_info.receiver_id = @USER_ID /*in this case, 2*/
ORDER BY pm_data.date_sent DESC
I'm assuming the relation between pm_dataand pm_infois the thread id. If it isn't, you should be able to adjust the above to whatever you need. I've also sorted by date sent here, but it won't keep the threads together. I'm not sure if you want to keep them together or not from the way you've phrased your question.
我假设之间的关系pm_data，并pm_info为线程ID。如果不是，您应该能够将上述内容调整为您需要的任何内容。我也按在这里发送的日期排序，但它不会将线程保持在一起。根据您提出问题的方式，我不确定您是否想将它们放在一起。
If you want to keep threads together, you'll need a more complicated query:
如果你想保持线程在一起，你需要一个更复杂的查询：
SELECT pm_info.is_read, sender.usrFirst + ' ' + sender.usrLast as sender_name,
pm_data.date_sent, pm_data.title, pm_data.thread_id
FROM pm_info
INNER JOIN pm_data ON pm_info.thread_id = pm_data.thread_id
INNER JOIN tblUsers AS sender ON pm_data.sender_id = tblUsers.usrID
INNER JOIN (SELECT thread_id, MAX(date_sent) AS max_date
FROM pm_data
GROUP BY thread_id) AS most_recent_date
ON pm_data.thread_id = most_recent_date.thread_id
WHERE pm_info.receiver_id = @USER_ID /*in this case, 2*/
ORDER BY most_recent_date.max_date DESC, pm_data.thread_id,
pm_data.date_sent DESC
This query uses a subselect to find the most recent modified date for each thread, then sorts by this first.
此查询使用子选择来查找每个线程的最近修改日期，然后首先按此排序。
回答by Eric
To get the list of messages for a user along with who and when they sent it, you can use the following query:
要获取用户的消息列表以及他们发送消息的人和时间，您可以使用以下查询：
select
s.usrFirst + ' ' + s.usrLast as SenderName,
m.Title,
m.DateSent,
i.IsRead
from
tblUsers r
inner join pm_info i on
r.receiver_id = i.receiver_id
inner join pm_data m on
i.thread_id = m.thread_id
inner join tblUsers s on
m.sender_id = s.userID
where
r.usrid = @id
This takes advantage of the fact that you canjoin a table to itself (here, tblUsersshows up twice: Once for the recipient, and again for the sender).
这利用了一个事实，即您可以将一个表加入到自身中（这里tblUsers显示两次：一次是给收件人，另一次是给发件人）。
If you'd like to see only unread messages, you can put and i.IsRead = 0in the whereclause.
如果您想只看到未读邮件，你可以把and i.IsRead = 0中where子句。
