我有三个表
members (id, fname, lname,...)
my_connections(id, member_id, connection_id, approved, ...`)
my_thoughts(id, member_id, thought, shared)`
我的查询
$sql = "
SELECT mc.* , cp.`id` AS cp_id, cp.*, m.*
FROM `my_connections` mc
LEFT JOIN `my_thoughts` cp ON mc.`connection_id` = cp.`member_id`
LEFT JOIN `members` m ON m.`id` = mc.`connection_id`
WHERE mc.`member_id` = '".$member_id."'
AND mc.`approved` = 1
AND cp.`shared` = 1
ORDER BY cp.`date_added` DESC
";
我得到了我所有的联系想法,我如何包括我的想法。我尝试了另一个连接,其中 thoughts.member_id = $member_id,但它将我的想法分配给我的连接。
可能有多种
方法可以实现此目的,但是使用两个单独的查询并使用 Union 将它们的结果合并到一个网格中怎么样?为此,两个查询必须选择相同的列。
例如,像这样的东西(已编辑):
SET @my_id = 1;
SELECT * FROM (
SELECT m.fname, m.lname, t.thought FROM members m
JOIN thoughts t on t.memberID = m.id
WHERE m.id = @my_id
UNION
SELECT m2.fname, m2.lname, t.thought FROM members m
JOIN connections c on c.memberID =m.ID
JOIN members m2 on m2.ID = c.connectionID
JOIN thoughts t on t.memberID = m2.ID
WHERE m.ID = @my_id
) tmp
ORDER BY tmp.lname ASC;
SQL 小提琴:http://sqlfiddle.com/#!2/405b2/17