CODEWITHSUNDEEP
mysql
Ambulance lada
Ambulance lada

Reputation: 331

How to deal with if subquery returns null in Having clause

Here is my query, how to deal with subquery returning null.

SELECT 
    p.parent_id, 
    i.*, 
    IF(p.parent_id IS NULL, 0, 1) AS has_child_rows,
    GROUP_CONCAT(
        JSON_OBJECT(
            'lang', itt.lang,
            'title', itt.title 
        )
    ) AS lang
FROM 
    image_tree i
    LEFT JOIN (
        SELECT parent_id 
        FROM image_tree 
        WHERE parent_id IS NOT NULL 
        GROUP BY parent_id
    ) p ON i.id = p.parent_id
    LEFT JOIN image_tree_title as itt  
        ON itt.image_tree_id = i.id
GROUP BY i.url 
HAVING i.parent_id = (
    SELECT FROM image_tree where image_tree.id = 77
)

How can I deal with the case when the subquery in my HAVING clause:

(SELECT FROM image_tree where image_tree.id = 77)

returns NULL, but I still want to compare that value with i.parent_id and match when they are both NULL.

Upvotes: 0

Views: 80

Answers (1)

Nick
Nick

Reputation: 147216

One way to make this work would be to replace both values with a value which is impossible for i.parent_id e.g. -1 when they are NULL i.e.

HAVING IFNULL(i.parent_id, -1) = IFNULL((SELECT parent_id 
                                         FROM image_tree
                                         WHERE image_tree.id = 77), -1)

Note you are missing parent_id in the columns part of the subquery.

Upvotes: 2

Related Questions