-1

I have two tables that I want to join and query. I want the query to return all records where the field columns value is between 2 values x, y. Table 1:

id  int(11) YES     NULL    
game    int(11) YES     NULL    
numbers varchar(255)    YES     NULL    
bonus   varchar(255)    YES     NULL    
multiplier  varchar(255)    YES     NULL    
created int(11) YES     NULL    
data    text    YES     NULL

Table 2:

bundle  varchar(128)    NO  MUL     
deleted tinyint(4)  NO  PRI 0   
entity_id   int(10) unsigned    NO  PRI NULL    
revision_id int(10) unsigned    NO  MUL NULL    
langcode    varchar(32) NO  PRI     
delta   int(10) unsigned    NO  PRI NULL    
field_secondary_prize_value_value   varchar(255)    NO      NULL

And the query :

SELECT
    t1.id,
    t2.field_secondary_prize_value_value
FROM
    table1 t1
INNER JOIN table2 t2 ON
    t1.id = t2.entity_id AND t2.field_secondary_prize_value_value BETWEEN 0 AND 10
ORDER BY
    t2.field_secondary_prize_value_value
DESC;

The result sets should start with records where t2.field_secondary_prize_value_value is 10 but this what I am getting:

ID.     field_secondary_prize_value_value  
1490476 5
1490496 5
1490531 5
1490596 5
1490636 5
1490651 5
1490666 5
1490676 5
1490756 5
1490761 5

If replace the between clause to equality: i.e:

INNER JOIN table2 t2 ON
    t1.id = t2.entity_id AND t2.field_secondary_prize_value_value = 10

1490546 10
1490561 10
1490581 10
1490616 10
1490896 10
1491041 10
1491156 10
1491221 10
1491316 10
1491341 10

I tried casting varchar to int but I still got the same results. If I order by a column on the base table I get results where field_secondary_prize_value_value shows different values within the range. Perhaps I need to use a subquery.

awm
  • 1,130
  • 3
  • 17
  • 37
  • Should the field_secondary_prize_value_value column be varchar? – Sam020 Jun 07 '22 at 15:36
  • the field type is varchar. I am not sure if that's has to do anything with it since equality works just fine. Should it be, possibly not .. t's a legacy system. – awm Jun 07 '22 at 15:36
  • 1
    I would try casting the value of that column to a number. For a test try `(t2.field_secondary_prize_value_value * 1) BETWEEN 0 AND 10`. – Sam020 Jun 07 '22 at 15:40
  • 1
    if i understand your question well, you are doing "BETWEEN 0 AND 10" so it will give you all values between 0 and 10 , so having 5 is normal , if you want have values bigger than 10 use "t2.field_secondary_prize_value_value >= 10" – Fatiha IMOUSSAINE Jun 07 '22 at 15:50
  • Fatiha IMOUSSAINE is right ... the datatype of the column might be of influence in the ordering though – Sam020 Jun 07 '22 at 15:56
  • Comparing context is string, not integer. Study https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html – Akina Jun 07 '22 at 16:58
  • @akina if I add a group by the field I see it has all the results. But order by isn't working. – awm Jun 07 '22 at 17:52
  • @FatihaIMOUSSAINE yes 5 is expected but I am ordering by the field value in desc order. The 1st record should be 10 – awm Jun 07 '22 at 18:58
  • @Sam020 I think it was indeed casting issue .. I tried casting the order by and it appears to solve the issue. Please add an answer so I can accept it. – awm Jun 07 '22 at 21:30

1 Answers1

1

The ordering is actually right, because the column is a varchar and values will be ordered alphanumeric. You can change it to a numeric sortorder by:

  • changing the datatype of the column to a numeric one if that is an option/applies
  • use type conversion in your queries (see link in Akina's comment for explanation and pitfalls)

More explanations of sorting types can be found here.

Applying this to your query (quick 'n dirty):

SELECT
  t1.id,
  t2.field_secondary_prize_value_value
FROM
  table1 t1
INNER JOIN table2 t2 ON
  t1.id = t2.entity_id AND t2.field_secondary_prize_value_value BETWEEN 0 AND 10
ORDER BY
  (t2.field_secondary_prize_value_value * 1)
DESC;

Personally I think it is better in this case to do the type conversion as close to the source as possible so it's easier to stay consistent, but that of course also depends on what you're trying to accomplish.

My alternative would be:

SELECT
  t1.id,
  t2.prize_value_num
FROM
  table1 t1
INNER JOIN (SELECT 
    entity_id, 
    (field_secondary_prize_value_value * 1) AS prize_value_num 
     -- or any other type conversion you see fit
  FROM table2
  ) t2 ON
  t1.id = t2.entity_id AND t2.prize_value_num BETWEEN 0 AND 10
ORDER BY
  t2.prize_value_num
DESC;

Be aware: in both examples the value '5B' will also meet the condition BETWEEN 0 AND 10.

Sam020
  • 376
  • 2
  • 6