I'm working with a database that seems to have odd normalization.

There is a guest table with a primary key guest_no. There is also a parent_no field to indicate who the parent guest is for child guests. The guest_no is the same as the parent_no for the parent record.

The people in the guest table can share addresses in the address table which has the foreign key guest_no.

I want to get the address which is linked to the parent record for the child record.

I know this is dead easy and I've done it before but can't quite wrap my head around it so far today.

This is the sql version of the query so far

SELECT guests.parent_no, guests.guest_no, guests.last_name, guests.first_name, address.address
FROM address
RIGHT JOIN guests ON address.guest_no = guests.guest_no
WHERE guests.parent_no=330005000

The right join gives me all the records connected to the parent record but the address field is obviously blank in the result for the child records.