MySQL从多个表联接 [英] Mysql join from multiple tables

问题描述

我有3张桌子

friends
posts
members

friends
========
id, to, from (user_id's), status

there are 3 status's -1 = denied, 0 = no response/new, 1 = accepted



posts
=======
p_id, user_id (same as member_id), text


members
========
member_id, f_name, l_name

如果要从帖子"中的帖子中选择文本，请将其与成员"中的用户名结合起来，仅显示user_id在朋友"表中的帖子.

If like to select the text from the post in 'posts' combine it with the users name from 'members' and only display posts where the user_id is in the 'friends' table.

我想知道是否可以完成，我在查询中尝试了一个IN()语句，该语句起作用了，但是它在IN()内生成csv时产生了一个新问题.我希望通过mysql来做到这一点，但是如果做不到，我可能会使用一个全局变量来存储朋友数据(但是当用户结识新朋友时，它将不会是最新的，或者必须进行刷新. ).

I would like to know if it can be done, I've tried an IN () statement in my query which worked, but it creates a new problem with generating the csv inside the IN (). I'd perfer to do this through mysql, but if it can't be done I may use a global variable to store friend data (but then it will not be upto date or will have to be refreshed when a user gets a new friend).

推荐答案

据我所知，您想查找所有您的朋友的姓名和职位，而不是该朋友表中的任何朋友根本...?

As I understand it, you want to find the name and posts of all your friends, not any friend that's in the friend table at all...?

您自己的用户ID位于$myId中，应该这样做(最新帖子优先)；

Your own user id being in $myId, this should do it (newest posts first);

添加了好友的状态检查

SELECT m.f_name, m.l_name, p.`text`
FROM members m
JOIN posts p 
  ON m.member_id = p.user_id
JOIN friends f 
  ON f.`to` = m.member_id OR f.`from` = m.member_id
WHERE (f.`from` = $myId OR f.`to`= $myId)
  AND f.`status` = 1 AND m.member_id <> $myId
ORDER BY p.p_id DESC

这篇关于MySQL从多个表联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！