getting warning of 1292 Truncated incorrect DOUBLE value using IN operator in mysql

I’m trying to get the total of a column which is dependent on the invoice table. I have successfully written the whole query but the problem is that if I write static values, it gives the perfect result but if I write column name, it gives the wrong result with a warning (Warning: #1292 Truncated incorrect DOUBLE value…).

Query That results in the warning is

SELECT
    job.customer_id,
    parties.name_of_establishment AS NAME,
        ((select IFNULL(SUM(job_details.sale_total),0) from job_details WHERE job_details.id IN (sale_invoice.items))) AS sale,
    (
    SELECT
        IFNULL(SUM(payments.amount),
        0) AS paid
    FROM
        payments
    WHERE
        payments.p_type = 'get' AND payments.p_c_id = sale_invoice.customer_id AND payments.company_id = '1'
) AS paid
FROM
    job
INNER JOIN sale_invoice ON sale_invoice.job_id = job.id 
INNER JOIN parties ON parties.id = sale_invoice.customer_id
WHERE
    company_id = '1' AND parties.name_of_establishment LIKE '%%'
GROUP BY
    job.customer_id
ORDER BY
    parties.name_of_establishment

Query That results perfectly is

SELECT
    job.customer_id,
    parties.name_of_establishment AS NAME,
        ((select IFNULL(SUM(job_details.sale_total),0) from job_details WHERE job_details.id IN (8,1,5,7))) AS sale,
    (
    SELECT
        IFNULL(SUM(payments.amount),
        0) AS paid
    FROM
        payments
    WHERE
        payments.p_type = 'get' AND payments.p_c_id = sale_invoice.customer_id AND payments.company_id = '1'
) AS paid
FROM
    job
INNER JOIN sale_invoice ON sale_invoice.job_id = job.id 
INNER JOIN parties ON parties.id = sale_invoice.customer_id
WHERE
    company_id = '1' AND parties.name_of_establishment LIKE '%%'
GROUP BY
    job.customer_id
ORDER BY
    parties.name_of_establishment

Where is the mistake? I have already wasted my 2 hours to figure out this.

Answer

I suspect that sale_invoice.items is a string containing comma separated integer values.
If this is the case then IN does not work.
Use the function FIND_IN_SET():

WHERE FIND_IN_SET(job_details.id, sale_invoice.items)

If sale_invoice.items contains spaces between the items, you must remove them:

WHERE FIND_IN_SET(job_details.id, REPLACE(sale_invoice.items, ' ', ''))

Advertisement

Answer