这是我的表结构(fun_users)

id  first_name  last_name   email   passkey place   profession  self_des    profile_img user_type   active  last_login  reg_date    reported_banned


我的友谊表是

id  user_id friend_id   status  date_request    from_ip


这是查询即时消息,用于获取已登录用户朋友的详细信息

SELECT `fun_friends`.`id` as fid, `fun_users`.`id` as uid, `fun_users`.`first_name`, `fun_users`.`profile_img`, `fun_users`.`profession`, `fun_users`.`place` FROM (`fun_friends`) JOIN `fun_users` ON `fun_users`.`id`=`fun_friends`.`friend_id` WHERE (`fun_friends`.user_id= '".$_SESSION['user_row_id']."' AND  `fun_friends`.`status` =1) OR (`fun_friends`.friend_id= '".$_SESSION['user_row_id']."' AND  `fun_friends`.`status` =1)


结果是

fid uid first_name       profile_img                       profession                             place
11  47  Agnii   thumbs/2013-03-311364721555.jpg       Software engineer                                 somewhere


该查询返回登录用户的详细信息,而不是其朋友的详细信息。谁能帮我 ?

最佳答案

下面的查询使用一个子查询,该子查询获取特定用户的所有朋友。表fun_users对子查询两次连接,因为子查询上有两个依赖于它的列。

SELECT  a.id AS FID,
        IF(a.user_ID = 'user_row_id_HERE', c.id, b.id) AS UID,
        IF(a.user_ID = 'user_row_id_HERE', c.first_name, b.first_name) AS first_name,
        IF(a.user_ID = 'user_row_id_HERE', c.last_name, b.last_name) AS last_name,
        IF(a.user_ID = 'user_row_id_HERE', c.profile_img, b.profile_img) AS profile_img,
        IF(a.user_ID = 'user_row_id_HERE', c.profession, b.profession) AS profession,
        IF(a.user_ID = 'user_row_id_HERE', c.place, b.place) AS place
FROM
        (
            SELECT  id, user_ID, friend_ID
            FROM    friendship
            WHERE   'user_row_id_HERE' IN (user_ID, friend_ID) AND
                    status = 1
        ) a
        INNER JOIN fun_users b
            ON a.user_ID = b.id
        INNER JOIN fun_users c
            ON a.friend_ID = c.ID


因此,问题来了,这条线上会发生什么?

IF(a.user_ID = 'user_row_id_HERE', c.id, b.id) AS UID


基本上,它从子查询中测试user_ID的值是否等于当前用户。如果恰好相等,则将返回表fun_users c中的列,反之亦然。

要进一步获得有关联接的知识,请访问以下链接:


Visual Representation of SQL Joins

关于php - 从所需的表格帮助中获取 friend 详细信息,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/15863077/

10-13 00:47