FIND_IN_SET() After checking the id's it's acting like it's two different posts-CodePudding

Here is the and DEMO page. You will understand better.

SELECT P.*,U.*,A.*
  FROM i_friends F FORCE INDEX(ixFriend)
    INNER JOIN i_posts P FORCE INDEX (ixForcePostOwner)
    ON P.post_owner_id = F.fr_two 
    INNER JOIN i_users U FORCE INDEX (ixForceUser)
    ON P.post_owner_id = U.iuid AND U.uStatus IN('1','3') AND F.fr_status IN('me', 'flwr', 'subscriber')  
    INNER JOIN i_user_uploads A FORCE INDEX (iuPostOwner)
    ON P.post_owner_id = A.iuid_fk 
    AND P.post_file <> '' AND A.uploaded_file_ext = 'mp3' 
    WHERE P.post_owner_id='1' 
       AND FIND_IN_SET(A.upload_id, P.post_file)
    ORDER BY P.post_id
    DESC LIMIT 5

CodePudding user response：

What you'll need to do is GROUP BY post_id if you want one row per post id.

However, since Post #6 has multiple MP3 uploads on it, you have a businesses decision to make. Which MP3 do you want? Or do you want them all in a list somehow?

You make that decision then use a grouping function like GROUP_CONCAT, or FIRST

So to get all of the MP3s listed, GROUP_CONCAT is most likely what you want:

SELECT P.*,U.*,GROUP_CONCAT(A.uploaded_file_path)
  FROM i_friends F FORCE INDEX(ixFriend)
    INNER JOIN i_posts P FORCE INDEX (ixForcePostOwner)
    ON P.post_owner_id = F.fr_two 
    INNER JOIN i_users U FORCE INDEX (ixForceUser)
    ON P.post_owner_id = U.iuid AND U.uStatus IN('1','3') AND F.fr_status IN('me', 'flwr', 'subscriber')  
    INNER JOIN i_user_uploads A FORCE INDEX (iuPostOwner)
    ON P.post_owner_id = A.iuid_fk 
    AND P.post_file <> '' AND A.uploaded_file_ext = 'mp3' 
    WHERE P.post_owner_id='1' 
       AND FIND_IN_SET(A.upload_id, P.post_file)
    GROUP BY P.post_id
    ORDER BY P.post_id
    DESC LIMIT 5