ONE TO ONE LEFT OUTER JOIN

Question

Hi everybody,

I was wondering, is there a way to make a kind of one to one left outer join:

I need a join that matches say table A with table B, for each record on table A it must search for its pair on table B, but there exists only ONE record that match that condition, so when it found its pair on B, it must stop and continue with the next row at table A.

What I have is a simple LEFT OUTER JOIN.

select *  from A LEFT OUTER JOIN B ON A.ID = B.ID ORDER BY (NAME) ASC

Thanks in advance!

Answer 1

SQL doesn't work this way. In the first place it does not look at things row-by-row. In the second place what defines the record you want to match on?

Assuming you don't really care which row is selcted, something like this might work:

SELECT * 
From tableA
left outer join 
(select b.* from tableb b1
join (Select min(Id) from tableb group by id) b2 on b1.id - b2.id) b
on a.id = b.id

BUt it still is pretty iffy that you wil get the records you want when there are multiple records with the id in table b.

Answer 2

The syntax you present in your question is correct. There is no difference in the query for joining on a one-to-one relationship than on a one-to-many.