CODEWITHSUNDEEP
mysqlif-statementsubquery
yossi
yossi

Reputation: 3164

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

Upvotes: 4

Views: 8913

Answers (2)

femtoRgon
femtoRgon

Reputation: 33351

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.

Upvotes: 6

Kermit
Kermit

Reputation: 34063

I believe you're looking for IFNULL.

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.

Which would transfer your current statement into:

SELECT IFNULL(t1.type, 'some default') AS ret FROM t1

Alternatively, you can use a CASE block.

SELECT
    (SELECT CASE
        WHEN t1.type IS NULL
            THEN 'some default'
        ELSE t1.type
    END AS ret) AS subq
...

Hope this helps.

Upvotes: 5

Related Questions