I have mysql query where I need to replace value in WHERE clause if subquery returns no results or null value.
Price query which works as required
Runs Successfully
SELECT `prices` FROM `pricing`
WHERE (3 BETWEEN `from_unit` AND `to_unit`)
AND `type` = 1
AND `id_pricing` IN
(
SELECT v1.`id_pricing` FROM `values` AS v1
INNER JOIN `values` AS v2 ON v1.`id_pricing` = v2.`id_pricing`
INNER JOIN `values` AS v3 ON v1.`id_pricing` = v3.`id_pricing`
INNER JOIN `values` AS v4 ON v1.`id_pricing` = v4.`id_pricing`
WHERE v1.`id_attribute` = 1 AND v1.`id_value` = 1
AND v2.`id_attribute` = 7 AND v2.`id_value` = 63
AND v3.`id_attribute` = 8 AND v3.`id_value` = 87
AND v4.`id_attribute` = 12 AND v4.`id_value` = 143
)
when I modify this query as below, adding IFNULL check on subquery inside IN clause, it throws error
'SQL Error (1242): Subquery returns more than 1 row'
SELECT `prices` FROM `pricing`
WHERE (3 BETWEEN `from_unit` AND `to_unit`)
AND `type` = 1
AND `id_pricing` IN
( IFNULL (
( SELECT v1.`id_pricing` FROM `values` AS v1
INNER JOIN `values` AS v2 ON v1.`id_pricing` = v2.`id_pricing`
INNER JOIN `values` AS v3 ON v1.`id_pricing` = v3.`id_pricing`
INNER JOIN `values` AS v4 ON v1.`id_pricing` = v4.`id_pricing`
WHERE v1.`id_attribute` = 1 AND v1.`id_value` = 1
AND v2.`id_attribute` = 7 AND v2.`id_value` = 63
AND v3.`id_attribute` = 8 AND v3.`id_value` = 87
AND v4.`id_attribute` = 12 AND v4.`id_value` = 143
),
'1234'
)
)
I tried replacing IFNULL with COALESCE still same result. Am I using wrong syntax.
Possibly move it to a LEFT JOIN and check that either there is a record, or that pricing is 1234:-
SELECT `prices`
FROM `pricing`
LEFT OUTER JOIN
(
SELECT v1.`id_pricing`, COUNT(*)
FROM `values` AS v1
INNER JOIN `values` AS v2 ON v1.`id_pricing` = v2.`id_pricing`
INNER JOIN `values` AS v3 ON v1.`id_pricing` = v3.`id_pricing`
INNER JOIN `values` AS v4 ON v1.`id_pricing` = v4.`id_pricing`
WHERE v1.`id_attribute` = 1 AND v1.`id_value` = 1
AND v2.`id_attribute` = 7 AND v2.`id_value` = 63
AND v3.`id_attribute` = 8 AND v3.`id_value` = 87
AND v4.`id_attribute` = 12 AND v4.`id_value` = 143
GROUP BY v1.`id_pricing`
) Sub1
ON Sub1.id_pricing = pricing.id_pricing
WHERE (3 BETWEEN `from_unit` AND `to_unit`)
AND `type` = 1
AND (Sub1.`id_pricing` IS NOT NULL
OR pricing.id_pricing = '1234')