Table fetch multiple rows on using join

Question

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

What do you want to achieve? Considering it's a conversation and your query means you want to get all the messages involving user 2869, you will really get multiple results. What I don't understand is, you said that even though you used limit 1, you're still getting multiple results? You probably misplaced your LIMIT 1. — suguspnk
– suguspnk, Commented Jul 10, 2017 at 5:33
Yes its a 3 table based on converstion . I want to fetch conv,username and photo respectively from these tables But photo table gives multiple rows because it has more than one photo against one id — Arslan Aslam
– Arslan Aslam, Commented Jul 10, 2017 at 5:38
What is your condition on which photo the user is currently using? Do you have another relevant column in photo table that might address this question? — suguspnk
– suguspnk, Commented Jul 10, 2017 at 5:42
i m matching the id from pm_conv table to photo's user_id colum e.g id =3562 and in photo.user_id has 4 rows with the column user_id — Arslan Aslam
– Arslan Aslam, Commented Jul 10, 2017 at 5:46

Parag Soni · Accepted Answer · 2017-07-10 05:52:25Z

1

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

answered Jul 10, 2017 at 5:52

Parag Soni

7137 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arslan Aslam Over a year ago

Thank You so much sir :) You saved my day :)

Parag Soni Over a year ago

Pleasure is all mine. :)

Bibhudatta Sahoo · Accepted Answer · 2017-07-10 05:58:05Z

0

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.

edited Jul 10, 2017 at 5:58

answered Jul 10, 2017 at 5:31

Bibhudatta Sahoo

4,9142 gold badges31 silver badges53 bronze badges

4 Comments

Arslan Aslam Over a year ago

Thanks for your response. But it limits the whole data i just want to limit photo.url

Bibhudatta Sahoo Over a year ago

Okay your photo.url have repeated value in table.

Arslan Aslam Over a year ago

yes...because its has more urls of image against each id

Bibhudatta Sahoo Over a year ago

@ArslanAli just try the modified query it will help you.

Collectives™ on Stack Overflow

Table fetch multiple rows on using join

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related