select from null?

Question

I am writing a trigger and i need 2 tables. new/old which already exist and p for the parent. However p may be null so if i do a

select new.a, new.b, ifnull(p.name, new.c) from p 

i'll get 0 results. if p is null. So how do i solve this? can i select from null or something else and left join p and use ifnull? i am not sure how to do this.

Answer 1

I'm not very familiar with sqlite, but maybe you can try something like this.

SELECT
    new.a, new.b, new.c AS newcol
FROM
    p
WHERE
    p.name = NULL
UNION
SELECT
    new.a, new.b, p.name AS newcol
FROM
    p
WHERE
    p.name <> NULL

Answer 2

I ended up using a dummy table.

select ... from blah as dummy on dummy.id = new.id //rest of my sql