[SQL] How to exclusivley select a record from different tables

I am stuck with a SQL problem - I hope you can help me. DBMS is Firebird.

Now here's my problem:

I have two tables containing adresses, both very similar in layout. One table contains the main address (main_addr) where the other table contains additional adresses (add_addr) and also contains a type indicator (delivery address, invoice address, ...). Both have a common id (addrid) which I can use to select an address. When I am looking for a main address I am sure to find it in the first table (main_addr). However there might be also an additional delivery address in the table add_addr

If there is an entry in the add_addr-table I need to use this entry. However if there is no entry in this table, I want to fallback on the table main_addr.

How can I do that?

I've tried something like

iif(add_addr.type = '1', add_addr.name1, main_addr.name1) as Name1,
iif(add_addr.type = '1', add_addr.name2, main_addr.name2) as Name2

from main_addr
join add_addr on main_addr.addrid = 105510
and add_addr.addrid = main_addr.addrid
and add_addr.type = '1'



This works as expected if there is a record in each table - in this case I get as expected the record from the table add_addr. However if there is only a record in the leading table (main_addr), I don't get a record at all.

How can I solve this?

Continue reading...