How to get parent address for child in sql query (oracle)

Question

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

I am looking for a query that lists the children's ids and addresses for a predefined set of children (ex. id IN (1, 4, 5, 8, 9)). If a child's address is null, then I want to display that child's parent's address instead.
How to write a query (for Oracle db) that gives me the desired outcome?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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.)

How to get parent address for child in sql query (oracle)

Sign up