I am not getting the correct result in the query-CodePudding

I have created the demo below to make it easier to understand and help. I hope it was clear for you to help me. https://www.db-fiddle.com/f/2LNHN7QdSMDgKLgupEFxoq/9

As can be understood from the SQL query, I want to print mp3 files to the screen. But if you run the query, you can see the result from you that the output of the query is wrong.

post_file id and upload_id are actually the same ids. Please look at the post_id and upload_ids to figure out what's wrong.

So post_file 2,9 in post_id 1 is actually upload_id 2 and upload_id 9 in the i_user_uploads table.

According to this condition, how can I print only upload_file_ext = 'mp3' data to the screen using post_id, post_file and upload_id.

The query I wrote is as follows. You can check the DEMO below to run the query with the tables.

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'
ORDER BY P.post_id

Thank you in advance.

CodePudding user response：

Just a add an equality test -

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'
and cast(p.post_file as int) = upload_id
        WHERE P.post_owner_id='1'
        ORDER BY P.post_id;

BTW does force index really improve performance here?