CODEWITHSUNDEEP
sqloracle-database
vehk
vehk

Reputation: 107

sql select values from another table if exist

Please help, need to select from table 1, but if entry with the same id exists in table2 should return name and last name from there otherwise values from table1

table1

id|name|lastname

1 |    |
2 |    |
3 |    |

table2

id|name|lastname
3 |    |

Tried this, but not working

SELECT ID, NAME, LASTNAME
    FROM table1 
    WHERE EXISTS
        (SELECT 1 FROM table2 WHERE table2.ID = table1.ID)

Upvotes: 0

Views: 41

Answers (1)

MT0
MT0

Reputation: 167972

if entry with the same id exists in table2 should return name and last name from there otherwise values from table1

You want a LEFT OUTER JOIN and then to use COALESCE:

SELECT t1.id,
       COALESCE( t2.name, t1.name ) AS name,
       COALESCE( t2.lastname, t1.lastname ) AS last_name
FROM   table1 t1
       LEFT OUTER JOIN table2 t2
       ON ( t1.id = t2.id )

Which, for your sample data:

CREATE TABLE table1 ( id, name, lastname ) AS
SELECT 1, 'Alice1', 'Abbot1' FROM DUAL UNION ALL
SELECT 2, 'Betty1', 'Baron1' FROM DUAL UNION ALL
SELECT 3, 'Carol1', 'Casey1' FROM DUAL;

CREATE TABLE table2 ( id, name, lastname ) AS
SELECT 3, 'Carol2', 'Casey2' FROM DUAL;

Outputs:

ID NAME LAST_NAME
3 Carol2 Casey2
2 Betty1 Baron1
1 Alice1 Abbot1

db<>fiddle here

Upvotes: 1

Related Questions