[SOLVED] How to exclusively select a record from different tables

How to exclusively select a record from different tables

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

I have two tables containing addresses, both very similar in layout. One table contains the main address (main_addr) where the other table contains additional addresses (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

Select    
   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?

Solution

I know nothing about Firebird, but in normal SQL you would do a left outer join on the add_addr table and select the main address if the additional one was not found.

So:

SELECT          COALESCE(add_addr.name, main_addr.name)
FROM            main_addr
LEFT OUTER JOIN add_addr
             ON main_addr.addrid = add_addr.addrid
            AND add_addr.type = '1'
WHERE           main_addr.addrid = 105510