MySQL script triggers Error #1241

I have the script below, which is supposed to get a price for an array of ID’s that has been provided.

But it needs to get 1 price per ID, and the tricky part is, that I want to have the ability to have scheduled price updates.

This mean that it needs to take the price that is <= UTC_TIMESTAMP smaller or equal to the current time.

SELECT 
`product_pricing`.`wo_id`,
`product_pricing`.`currency` AS price2_currency,
`product_pricing`.`price` AS price2,
`product_pricing`.`formula_id`,
`product_pricing`.`vat_calculated` AS price2_vat_calculated,
`product_pricing`.`vat_id`,
`product_pricing`.`timestamp_valid`,

`product_price_formulas`.`formula_id`,
`product_price_formulas`.`formula` price2_formula

FROM 
`product_pricing`

LEFT JOIN `product_price_formulas` 
ON `product_pricing`.`formula_id` = `product_price_formulas`.`formula_id`

WHERE 
`product_pricing`.`wo_id` IN (

    SELECT 
    `product_pricing`.`wo_id`,
    `product_pricing`.`timestamp_valid`

    FROM `product_pricing`

    WHERE 
    `product_pricing`.`wo_id` 
        IN ('015724', '028791', '015712', '015715', '015717', '039750', '028791')
    AND `product_pricing`.`timestamp_valid` <= UTC_TIMESTAMP

    ORDER BY `product_pricing`.`timestamp_valid` DESC        
 )

Is this possible?

---

Sample data: Current output

——————————————————————————————————————————————————————————————————
 | wo_id    | price2    | timestamp_valid 
——————————————————————————————————————————————————————————————————
 | 028791   | 8000      | 2018-03-20 19:55:41
 | 028791   | 6000      | 2018-04-01 19:55:41
 | 028791   | 4000      | 2018-04-20 19:55:41
 | 015724   | 3000      | 2018-04-18 19:55:41
 | 015724   | 1500      | 2018-03-01 19:55:41
 ....

Wanted output:

——————————————————————————————————————————————————————————————————
 | wo_id    | price2    | timestamp_valid 
——————————————————————————————————————————————————————————————————
 | 028791   | 6000      | 2018-04-01 19:55:41
 | 015724   | 1500      | 2018-03-01 19:55:41

Answer

I guess your issue is on the IN clause.

You select two field in IN clause.

EDIT

You need to self join a subquery by wo_id and Max timestamp_valid.

SELECT 
    `product_pricing`.`wo_id`,
    `product_pricing`.`currency` AS price2_currency,
    `product_pricing`.`price` AS price2,
    `product_pricing`.`formula_id`,
    `product_pricing`.`vat_calculated` AS price2_vat_calculated,
    `product_pricing`.`vat_id`,
    `product_pricing`.`timestamp_valid`,
    `product_price_formulas`.`formula_id`,
    `product_price_formulas`.`formula` price2_formula
FROM 
`product_pricing`
LEFT JOIN `product_price_formulas` ON `product_pricing`.`formula_id` = `product_price_formulas`.`formula_id`
INNER JOIN 
(
    SELECT 
        `product_pricing`.`wo_id`,
        MAX(`timestamp_valid`) AS MaxDate
    FROM `product_pricing`
    WHERE 
        `product_pricing`.`timestamp_valid` <= UTC_TIMESTAMP
    GROUP BY 
        `product_pricing`.`wo_id`
)as temp ON temp.wo_id = `product_pricing`.`wo_id` AND temp.MaxDate = `product_pricing`.`timestamp_valid`
WHERE  
    `product_pricing`.`wo_id` IN ('015724', '028791', '015712', '015715', '015717', '039750', '028791')