MYSQL HAVING STATEMENT AND CALCULATION ISSUE

Question

What is wrong in this mySQL statement?

I check for the max value of C_BATCH_ID in batch table and then compare it to the value extracted from a reference (C_KEY)

It returns nothing while if I replace MAX( C_BATCH_ID ) by a value such as 23 it returns n records.

SELECT
    contract.KEY AS C_KEY,
    contract.BATCH_ID AS C_BATCH_ID,
    contract.CUSTOMER_NAME as C_NAME,
    batch.BATCH_ID = B_BATCH_ID
FROM
    contract
INNER JOIN 
    batch ON contract.BATCH_ID = batch.BATCH_ID 
HAVING
    SUBSTRING_INDEX( C_KEY, '-', 1 ) = MAX( B_BATCH_ID )
ORDER BY
    contract.CUSTOMER_NAME

Thanks for your help. :)

contract table

id	C_KEY	C_BATCH_ID	C_NAME
1	10-KEY01	10	Customer 1
2	23-KEY01	23	Customer 2
3	23-KEY02	23	Customer 3
4	12-KEY01	12	Customer 2
5	23-KEY03	23	Customer 4

batch table

B_BATCH_ID	BATCH_NAME
1	Batch 1
2	Batch 2
3	Batch 3
4	Batch 4
10	Batch 10
12	Batch 12
23	Batch 23

Desired result as 23 is the max value of B_BATCH_ID:

id	C_KEY	C_BATCH_ID	C_NAME	B_BATCH_ID
2	23-KEY01	23	Customer 2	23
3	23-KEY02	23	Customer 3	23
5	23-KEY03	23	Customer 4	23

Answer 1

You need to get MAX(BATCH_ID) in sub-query. Solution could be like this:

SELECT
    contract.KEY AS C_KEY,
    contract.BATCH_ID AS C_BATCH_ID,
    contract.CUSTOMER_NAME as C_NAME
FROM
    contract
WHERE
    SUBSTRING_INDEX( contract.KEY, '-', 1 ) = (SELECT MAX(BATCH_ID) FROM batch)
ORDER BY
    contract.CUSTOMER_NAME

Base on example data contract.BATCH_ID could be used instead of SUBSTRING_INDEX( contract.KEY, '-', 1 ) but not sure if there is a reason for this in real data