CODEWITHSUNDEEP
sqlsqlite
GuillaumeA
GuillaumeA

Reputation: 3545

SQLite query from two tables - Choose which value to return

I have two tables as for example

Table1

ID  |  Prop1  |  Prop2  |  Prop3
--------------------------------
 1  |    a    |   b     |   c
 2  |    d    |   e     |   f

Table2

Name  |  ID  |  Prop1  |  Prop2
-------------------------------
 i    |   1  |   aa    |  null

What I would like to do is returning (not modifying) the row of Table1 corresponding to the ID of the Table2.Name = 'i' but with the values of Table2 that are not null (if they exist). The result should then look like :

ID  |  Prop1  |  Prop2  |  Prop3
--------------------------------
 1  |    aa   |   b     |   c

Upvotes: 2

Views: 118

Answers (1)

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107508

You can use IFNULL(x, y) to substitute a value for NULL:

SELECT
    t1.ID
   ,IFNULL(t2.Prop1, t1.Prop1) AS Prop1
   ,IFNULL(t2.Prop2, t1.Prop2) AS Prop2
   ,IFNULL(t2.Prop3, t1.Prop3) AS Prop3
FROM
    Table1 t1
LEFT JOIN
    Table2 t2
    ON
    t1.ID = t2.ID

Note that IFNULL() only ever accepts two arguments. If you think you might be adding more tables at some point, switch to COALESCE():

SELECT
    t1.ID
   ,COALESCE(t2.Prop1, t1.Prop1) AS Prop1
   ,COALESCE(t2.Prop2, t1.Prop2) AS Prop2
   ,COALESCE(t2.Prop3, t1.Prop3) AS Prop3
FROM
    Table1 t1
LEFT JOIN
    Table2 t2
    ON
    t1.ID = t2.ID

Upvotes: 1

Related Questions