MySQL check if subquery return NULL, then replace value

Question

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.

Why not `SELECT IFNULL(v1.id_pricing, '1234')` in the subquery? — Explosion Pills, Jul 09 '13 at 06:57
Here is a similar problem with a solution: http://stackoverflow.com/questions/9861171/how-to-resolve-this-in-mysql-1242-subquery-returns-more-than-1-row — Gimmy, Jul 09 '13 at 07:10
I'm confused by that subquery. What's it supposed to be doing? Consider providing a sqlfiddle by way of demonstration — Strawberry, Jul 09 '13 at 07:17
@ExplosionPills did try your solution, but to no avail. If no results are found, it just returns empty set, but not '1234' — Abhishek Madhani, Jul 09 '13 at 07:41
@Gimmy, thanks for pointing to a similar situation, however that solution is applicable when I need to need to keep check for each join. In my case I am looking to replace result if whole subquery return no result (null). My subquery has multiple joins, and its possible sometimes that 1 join may return no result, but combination of them still produce result. — Abhishek Madhani, Jul 09 '13 at 07:48
@Strawberry, that subquery in MySQL is alternative of using 'INTERSECT' clause available in Oracle / MS-SQL Databases. — Abhishek Madhani, Jul 09 '13 at 07:56

score 1 · Answer 1 · answered Jul 09 '13 at 08:17

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')

MySQL check if subquery return NULL, then replace value

1 Answers1