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?
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
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