mysql - 使用 join 获取多行表

Question

我想从 3 个表 pm_conv、user、photo 获取数据，但在加入第 3 个表 photo 我得到多行，我尝试分配以获取 LIMIT 1 中的数据，但无法。

这是查询

SELECT 
    pm_conv. *, user.username, user.id, photo.url 
FROM 
    pm_conv 
JOIN 
    user ON CASE 
        WHEN pm_conv.sender_id ='2869' 
            THEN pm_conv.recipient_id = user.id 
        WHEN pm_conv.recipient_id ='2869' 
            THEN pm_conv.sender_id = user.id 
        END
JOIN 
    photo ON CASE 
        WHEN pm_conv.sender_id ='2869' 
            THEN pm_conv.recipient_id = photo.user_id 
        WHEN pm_conv.recipient_id ='2869' 
            THEN pm_conv.sender_id = photo.user_id 
        END
WHERE 
    `sender_id`='2869' 
    OR `recipient_id` ='2869' 
ORDER BY  
    `last_answer_date` DESC

Answer 1

尝试使用子查询代替照片表。也许它应该起作用。

SELECT 
        pm_conv. *, 
        user.username, 
        user.id, 
        IF(pm_conv.sender_id ='2869', 
            (SELECT photo.user_id FROM photo WHERE pm_conv.recipient_id = photo.user_id LIMIT 1 ) , 
            IF (pm_conv.recipient_id ='2869', 
                (SELECT photo.user_id FROM photo WHERE pm_conv.recipient_id = photo.user_id LIMIT 1 ), 
                '')) as PHOTO_USER
    FROM 
        pm_conv 
    JOIN 
        user ON CASE 
            WHEN pm_conv.sender_id ='2869' 
                THEN pm_conv.recipient_id = user.id 
            WHEN pm_conv.recipient_id ='2869' 
                THEN pm_conv.sender_id = user.id 
            END

    WHERE 
        `sender_id`='2869' 
        OR `recipient_id` ='2869' 
    ORDER BY  
        `last_answer_date` DESC