等待好友请求查询好友表，并从其他表中获取数据 [英] Pending friend request query for friends table and grabbing data from other tables

问题描述

要获得对选定用户的待处理请求，我使用:

To get pending request for selected user I use:

SELECT f1.asker_user_id AS friend_id
FROM friends AS f1 
LEFT JOIN friends AS f2
    ON f1.asked_user_id = f2.asker_user_id
    AND f1.asker_user_id = f2.asked_user_id   
WHERE f1.status = 1 AND f2.status IS NULL
AND f1.asked_user_id = 2

http://www.sqlfiddle.com/#!2/0f7a0d5/65 它会正确返回用户3.VALUES(3,2,1)

http://www.sqlfiddle.com/#!2/0f7a0d5/65 It correctly returns user 3. VALUES (3,2,1)

但是我想获得有关该用户3的更多数据.

But I'd like to get more data about this user 3.

我需要users表中的name_surname和avatar. 并且connections表中此用户的所有word_id，作为回报，这些用户将基于word_id在words_en中显示word.

I need name_surname and avatar from users table. And all word_ids of this user from connections table, which in return would show word from words_en based on word_id.

感谢分配！

p.s.我试过了:

SELECT a.name_surname,GROUP_CONCAT(Distinct w.word Order by w.word asc) AS words
FROM (
  SELECT f1.asked_user_id AS friend_id,
       f1.created,
       u.name_surname,
       u.avatar
  FROM friends AS f1 
  LEFT JOIN friends AS f2 ON f1.asked_user_id = f2.asker_user_id
  INNER JOIN users AS u ON f1.asked_user_id = u.id
       AND f1.asker_user_id = f2.asked_user_id
  WHERE f1.status = 1 AND f2.status IS NULL
       AND f1.asker_user_id = 2
) a
LEFT JOIN connections c ON c.user_id = a.friend_id 
LEFT JOIN words_en w ON c.word_id = w.id
GROUP BY 1;

推荐答案

基于这个问题和您之前的问题，我认为您可能正在寻找这样的东西:

Based on this question and your previous questions, i think you might be looking for something like this:

SELECT u.name_surname, 
       u.avatar,
       GROUP_CONCAT(DISTINCT w.word Order by w.word asc) AS words
FROM users u
INNER JOIN 
(
SELECT f1.asker_user_id AS friend_id
    FROM friends AS f1 
    LEFT JOIN friends AS f2
        ON f1.asked_user_id = f2.asker_user_id
        AND f1.asker_user_id = f2.asked_user_id   
    WHERE f1.status = 1 AND f2.status IS NULL
    AND f1.asked_user_id = 2
) a ON a.friend_id = u.id
LEFT JOIN connections c ON u.id = c.user_id
LEFT JOIN words_en w ON w.id = c.word_id
GROUP BY u.id;

sqlfiddle演示

这篇关于等待好友请求查询好友表，并从其他表中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！