要获取我使用的选定用户的待处理请求:
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。值 (3,2,1)
但我想获得更多关于这个用户的数据 3.
我需要name_surname
和users
桌子上的avatar
。以及该用户connections
表中的所有word_id
,作为回报,该表将根据word_id
显示来自words_en
word
。
谢谢分配!
附言我试过这个:
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;
基于这个问题和你之前的问题,我想你可能会寻找这样的东西:
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 演示
这是以下查询的 SQL 小提琴,这就是我认为您所追求的。
SELECT users.name_surname, users.avatar,
GROUP_CONCAT(DISTINCT words_en.word Order by words_en.word asc) AS words
FROM
(
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
) AS MainQ
INNER JOIN users ON MainQ.friend_id = users.id
INNER JOIN connections ON users.id = connections.user_id
INNER JOIN words_en ON words_en.id = connections.word_id