CODEWITHSUNDEEP
mysql
Arslan Aslam
Arslan Aslam

Reputation: 17

Table fetch multiple rows on using join

I want to fetch data from 3 tables pm_conv,user,photo but after joining the 3rd table photo i get multiples rows i tried allot to get data in LIMIT 1 but cant .

Here is the query

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

Upvotes: 0

Views: 120

Answers (2)

Bibhudatta Sahoo
Bibhudatta Sahoo

Reputation: 4894

Just try this

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 limit 1

Modified query:-

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 ((SELECT `photo`.* FROM `photo` join `pm_conv` as pmc ON CASE 
        WHEN pmc.sender_id ='2869' 
            THEN pmc.recipient_id = photo.user_id 
        WHEN pmc.recipient_id ='2869' 
            THEN pmc.sender_id = photo.user_id 
        END limit 1)) as 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

I thing it will give your desired output.

Upvotes: 0

Parag Soni
Parag Soni

Reputation: 713

Try subquery instead , for photo table. May be it should work. 

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

Upvotes: 1

Related Questions