Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Subquery inside IF as a value for a column with null value

Given:

t1{id,type}
t2{type,table1_id}

I'm using this:

SELECT IF(t1.type IS NULL, 'some default', t1.type) as ret from t1

I want to do something like this:

SELECT IF(
    t1.type IS NULL, 
    IF(
        (SELECT t2.type FROM t2 WHERE t2.table1_id=t1.id LIMIT 1) IS NOT NULL,
        table2.type,
        'some defaults'
    ),
    t1.type
) as ret from table1
like image 602
yossi Avatar asked Sep 02 '25 16:09

yossi


1 Answers

This --

SELECT IF(
    t1.type IS NULL, 
    IF(
        (SELECT t2.type FROM t2 
           WHERE t2.table1_id=t1.id LIMIT 1)
           IS NOT NULL,
        t2.type,
        'some defaults'),
    t1.type
) as ret from t1, t2 where t1.id = t2.table1_id

seems to work.

like image 67
femtoRgon Avatar answered Sep 04 '25 13:09

femtoRgon