user3249152 user3249152 - 8 months ago 88
SQL Question

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

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?

MT0 MT0
Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download