MySQL Select JOIN 3表

问题描述：

我有三个基本表:

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.

因此，如果usrID 2(Bill Jones)打开了他的收件箱，他将看到他2未读(因此为"is_read"列)消息(线程#111和#222).

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).

基本上，我需要知道如何设置我的SELECT语句以联接所有三个表(pm_data和pm_info之间的关系带来了消息信息，而tblUsers和pm_data之间的关系带来了信息的显示名称"发送者)，以在顶部显示最新的(按时间戳记?)线程.

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.

因此，我们将看到以下内容:

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!

这是我的最终答案:

我听取了lc的建议，并根据id建立了两个表之间的关系(在pm_info中添加了名为"message_id"的列).

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).

然后，对MySQL语句进行一些调整，以得出以下结论:

Then, tweaked the MySQL statement around a little bit to come up with this:

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).

答

您将需要两个联接.如下所示的内容应该可以帮助您入门(尽管我不是100％理解pm_data和pm_info之间的关系):

You'll need two joins. Something like the following should get you started (although I don't 100% understand the relationship between pm_data and 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

我假设pm_data和pm_info之间的关系是线程ID.如果不是，您应该可以根据需要调整以上内容.我还按此处发送的日期进行了排序，但是它不会将所有线程保持在一起.我不确定您是否要按照问题的表达方式将它们保持在一起.

I'm assuming the relation between pm_data and pm_info is 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.

如果您希望将线程保持在一起，则需要更复杂的查询:

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.

MySQL Select JOIN 3表

相关推荐