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

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 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

@forpas Please see the ids I marked in red in this screenshot. https://ibb.co/XS1M4g4 . `15,2` is the upload_id. Note that `upload_id 15` is a png file but `upload_id 2` is an `mp3` . Query should check `2`, but query checking only `15` and not checking `2`. The query should check all ids https://ibb.co/ZM40sN1 — AlwaysStudent, Apr 30 '22 at 17:04
You should read my answer to [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) — Bill Karwin, Apr 30 '22 at 17:24

score 1 · Accepted Answer · answered Apr 30 '22 at 17:11

1

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)

answered Apr 30 '22 at 17:11

forpas

160,666
10
38
76

Your answer gives the result I want. But there is something wrong with this answer because if both ids are mp3 then it acts as if there is a different `post_id`. So, for example, `post_id` number `1` is `post_file 2,3` . Let the `uploaded_file_ext` in `upload_id 2` and `upload_id 3` be `mp3`. Result `post_id 1` is showing twice. Please check the [DEMO](https://www.db-fiddle.com/f/2LNHN7QdSMDgKLgupEFxoq/14) I have created for this bug. https://ibb.co/SX0jhNW – AlwaysStudent Apr 30 '22 at 17:43
@AlwaysStudent my answer is about the question: "cast() only takes first id and ignores other ids in sql query". I proposed FIND_IN_SET() because it checks all the ids in the list and this works. If you need furthermore refinement for your query you are the only one who knows your requirement and since this is a new requirement you should ask a new question where you explain what you want. – forpas Apr 30 '22 at 17:55
[HERE](https://stackoverflow.com/questions/72070978/find-in-set-after-checking-the-ids-its-acting-like-its-two-different-posts) is the new question. – AlwaysStudent Apr 30 '22 at 18:11

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

1 Answers1