CODEWITHSUNDEEP
sqlsqlite
littlecat
littlecat

Reputation: 81

SQLite refer to select column in case

I have this SQLite query:

SELECT CASE WHEN (stops.parent_station IS '') THEN stops.stop_name ELSE (SELECT stops.stop_name FROM stops WHERE stops.stop_id = parent_id) END as stops from stops

This works fine, but I need a way to get the stop name in the else statement from the selected stop's parent station id. So basically I need a way to reference the current row's parent_id

Upvotes: 1

Views: 545

Answers (2)

Aditi Singh
Aditi Singh

Reputation: 117

You can try using coalesce. Coalesce returns first non empty value For eg coalesce(null, 'a') will return a and coalesce('ab', 'cd') will return ab. In your case you have to check for '' string to in this case you can use nullif returns null if 2 arguments are same which will solve your problem.

Select
    coalesce (NULLIF(s1.parent_station,''), s2.stop_name) as stop 
from stop s1 
   left join stop s2 on s1.stop_id = s2.parent_id

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a self join 

SELECT 
    CASE WHEN a.parent_station is '' then a.stop_name else b.stop_name end as stop 
from stop a 
left join stop b on a.stop_id = b.parent_id

Upvotes: 1

Related Questions