Oracle SQL: Joining at most one associated entity

Question

I have tables Building and Address, where each Building is associated with 0..n Addresses.

I'd like to list Buildings with an associated Address. If a Building has several entrances, and thus several Addresses, I don't care which one is displayed. If a Building has no known addresses, the address fields should be null.

This is, I want something like a left join that joins each row at most once.

How can I express this in Oracle SQL?

PS: My query will include rather involved restrictions on both tables. Therefore, I'd like to avoid repeating those restrictions in the query text.

Answer 1

what you could do is an restriction on the addresses dat you join. For instance by requiring that there is no address with a lower id:

select *
from building b
left join addresses a on (a.buildingid = b.id)
where not exists (select 1 from addresses a2
                  where a2.buildingid = b.id and a2.id < a.id)

in this case you will get at most 1 address per building.

Answer 2

select b.*, max(a.id) as aid 
from building b 
left outer join addresses a on (a.buildingid = b.id) 
group by a.buildingid 

or

select b.*, maxid
from building b 
left outer join 
(
 select buildingid, max(id) as maxid
 from addresses
 group by buildingid 
) a on (a.buildingid = b.id) 

Answer 3

Because you don't care which of many addresses is displayed:

Oracle 9i+:

WITH summary AS (
      SELECT b.*,
             a.*,
             ROW_NUMBER() OVER (PARTITION BY b.building_id) rn
        FROM BUILDINGS b
   LEFT JOIN ADDRESSES a ON a.building_id = b.building_id)
SELECT s.*
  FROM summary s
 WHERE s.rn = 1

Non-Subquery Factoring Equivalent:

SELECT s.*
  FROM (SELECT b.*,
               a.*,
               ROW_NUMBER() OVER (PARTITION BY b.building_id) rn
           FROM BUILDINGS b
      LEFT JOIN ADDRESSES a ON a.building_id = b.building_id) s
 WHERE s.rn = 1

Answer 4

I would consider querying the address in the SELECT clause, e.g.:

SELECT b.*
      ,(SELECT a.text
        FROM   addresses a
        WHERE  a.buildingid = b.id
        AND    ROWNUM=1) as atext
FROM   building b;

The ROWNUM=1 means "just get one if there are any, don't care which".

The advantage of this approach is that it will probably perform better than most alternatives, as long as a suitable index on addresses.buildingid exists. It will stop looking for more addresses as soon as it finds one for each building queried.

The downside to this approach is if you want multiple columns from the address table, you can't - although you can concatenate them together into one string.