Aldo Aldo - 2 months ago 11
SQL Question

Return another value when row is inexistent

In a database table with a structure like this :

Table 1

Name | Id
A 1
B 2


Table 2

Table1's ID | IntValue
1 11
2 66


now, there is a query which joins the 2 tables and outputs something like

A | 11
B | 66


but the problem is that when, let's say row (A,1) gets deleted from table1 the query outputs

| 11
B | 66


so instead of writing A it leaves it null because the row doesn't exist.

My question is: Instead of leaving it null, is there any option to make it write "Item Inexistent" or smth?

My database is Firebird 2.1.2

Answer
SELECT  COALESCE(t1.name, 'Item nonexistent'), t2.intValue
FROM    table2 t2
LEFT OUTER JOIN
        table1 t1
ON      t1.id = t2.id