How to make a one to one left outer join?

Question

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 1 record that matches that condition, so when it has 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.