Select comma separated records from table along with user image

Question

Table : frei_session

id  username        accountId   status  status_mesg

14  Sumit Bijvani   50          0       I am available
16  Dilip Borad     49          1       I am available
15  Karan Bijvani   51          1       I am available

Table : users

accountId    friends      userImage

49           50,52        49.jpg
50           49,52,51,44  50.jpg
51           50           51.jpg

I have 2 tables, frei_session have records of online users and users table have data of users and friends of them separated ID by comma.

I want to retrieve data from frei_session table based on friends column of users table.

For Ex

If user 49 is online, I want Output like below

id  username        accountId   status  status_mesg        userImage

14  Sumit Bijvani   50          0       I am available     50.jpg

Because User 50 and 52 is friend of User 49 but now only User 50 is online

I have tried following query. but it show me wrong userImage.

SELECT  b.*, a.userImage
FROM    users a
        INNER JOIN frei_session b
            ON FIND_IN_SET(b.accountID, a.friends) > 0
         INNER JOIN
        (
            SELECT  accountID, username, MAX(id) id
            FROM    frei_session
            GROUP   BY accountID, username
        ) c ON b.accountID = c.accountID AND
                b.username = c.username AND
                b.id = c.id
WHERE   b.status = 0 AND
        a.accountID = 49

SQL Fiddle Demo

Answer 1

The result from the frei_session is the friend of user (a) that's why it does not contain the image of your friend but the image of the user you are trying to search. So in order for you to get your friends image, you need to join it to another user (d) so you can get the correct image from the frei_session table.

SELECT  b.*, d.userImage
FROM    users a
        INNER JOIN frei_session b
            ON FIND_IN_SET(b.accountID, a.friends) > 0
         INNER JOIN
        (
            SELECT  accountID, username, MAX(id) id
            FROM    frei_session
            GROUP   BY accountID, username
        ) c ON b.accountID = c.accountID AND
                b.username = c.username AND
                b.id = c.id
        INNER JOIN users d
          ON b.AccountID = d.AccountID
WHERE   b.status = 0 AND
        a.accountID = 49

• SQLFiddle Demo