MySQL LEFT JOIN Query is Behaving Strangely

62
August 08, 2019, at 5:30 PM

Mysql LEFT JOIN query is returning strange result. The query returns a record when the right ON condition "PARTIALLY" matches the left.

SELECT s.id sid
     , s.points sCredits
     , s.ghs
     , s.usd
     , s.africa
     , o.id oid
     , o.user_id
     , o.item_id
  FROM pi21o_zoo_item s
  LEFT 
  JOIN pi21o_logos_orders o
    ON s.id = o.item_id
   AND o.user_id = '268' 
 WHERE s.id = '268'

The problem is the query returns a record when s.id = '268' and o.item_id = '268-AGW'

Answer 1

Your problem is that when you compare an integer (s.id) with a string (o.item_id) MySQL automatically converts the string to an integer (see the manual). Since '268-AGW' starts with an integer, it is successfully converted to 268 which then matches the s.id value. To work around this, cast the s.id value to a string i.e. write

(CAST(`s`.`id` AS CHAR) = `o`.`item_id`) AND (`o`.`user_id` = '268') 
READ ALSO
Make table values as column in MySQL

Make table values as column in MySQL

I have the following table and table data on my database:

39
Embedded credentials in url is deprecated. I need to get and display image from authorized server

Embedded credentials in url is deprecated. I need to get and display image from authorized server

In html element I need put authorized link (url with credentials ie

29
Annotate Flask-Resource in another file

Annotate Flask-Resource in another file

I have two filesThe main file creates and starts the API

49
Auto insert the logged user in a Foreign Key

Auto insert the logged user in a Foreign Key

I'm creating an API for something like 9gag project that I'm doing, and upon uploading a post the logged user to be auto inserted in the created_by field that I have in my Post model

47