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)
if entry with the same
idexists intable2should returnnameandlast namefrom there otherwise values fromtable1
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
