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

Question

Here is the DEMO for better understand what is the issue.

The following sql query prints only the ones with the mp3 value of the uploaded_file_ext in the i_user_uploads table. As can be understood from the query, the data in i_user_uploads is taken from the post_file ids in the i_posts table.

I need to make a few clarifications for better understanding. post_file actually consists of the upload_ids in the i_user_uploads table. For example, post_file 2,14 actually represents the upload_id in i_user_uploads. It will be easier to understand if you look at the chart in the DEMO.

The output in the query should be like this. The upload_ids in post_file should be followed and the uploaded_file_ext = 'mp3's in the i_user_uploads table should be printed on the screen.

To explain in more detail, post_file 1 is actually upload_id 1 in the i_user_uploads table. If the uploaded_file_ext in this id is mp3, this is printed on the screen.

The problem is that if the cascaded ids contain mp3, the output behaves as if there were more than one post_id. So it repeats the same post_id.

Please check this 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

Answer 1

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