cast() only takes first id and ignores other ids in sql query-CodePudding

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 the post_file in the i_posts table sometimes contains more than one id and these ids are separated by commas. For example post_file 15,2,3 or post_file 15,2. But cast(P.post_file as signed int) = A.upload_id takes only the first id and ignores the other ids after the comma.

If 15 in post_file 15,2,3 is not an mp3 file, and 2 is an mp3 extension, it ignores 2 and does not print to the screen.

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 cast(P.post_file as signed int) = A.upload_id
    ORDER BY P.post_id
    DESC LIMIT 5

CodePudding user response：

Instead of CAST() you can use FIND_IN_SET() to check if a certain value exists inside a comma separated string:

WHERE P.post_owner_id='1' AND FIND_IN_SET(A.upload_id, P.post_file)