Given the two tables parent and child like this:
PARENT
id (pk)
address
CHILD
id (pk)
address
parent (fk)
Ex:
PARENT
id address
1 West street
2 East street
3 South street
CHILD
id address parentid
1 1st street 3
2 2nd street (null)
3 (null) 2
4 4th Street 1
Expected outcome for child id in(1, 3)
childid address
1 1st Street
3 East Street
You can use a correlated sub-query and coalesce:
SELECT id,
COALESCE(
address,
( SELECT p.address FROM parent p WHERE c.parent = p.id )
) AS address
FROM children c
WHERE id IN (1, 4, 5, 8, 9);
or you can use a join:
SELECT c.id,
COALESCE( c.address, p.address ) AS address
FROM children c
LEFT OUTER JOIN parent p
ON ( c.parent = p.id )
WHERE c.id IN (1, 4, 5, 8, 9);
(Use a LEFT OUTER JOIN
in case you do not have information on the parent.)