Hello,

I hope to find help for the following problem. I have a database with international addresses. For the US and CAN-addresses, and only for them, also the States are stored. In order to have the tables normalized I have split the data to different tables (fields are incomplete, i give only those that are important to the question):
a) persons - person_id, first_name, last_name, ... country_id
b) countries - country_id, country_name
c) states - state_id, state_name

and finally a lookup table with fields
id, person_id, country_id, state_id
Only for US and CAN addresses data are stored in the lookup table, as the other addresses have no states.

My problem now is: I canīt figure out a way to join the tables so that I get for US- and CAN-addresses the state_name as well. If I want to get a list of all addresses, my join-tries leads to endless iterations, and I get for each non-US or CAN-address iterations over all state_ids.
There is of course, no problem with either getting all addresses without the states info, or all US- and CAN- addresses with state info, and all other addresses via separate queries.

Obviously the problem lies in the fact that some of the person entries can not be found in the state lookup table (which would be senseless).

Is there any way to get the result I want?

Thanks for help,
Christoph