Soul3lade Soul3lade - 4 months ago 7
SQL Question

Join that will return the same amount of records found on the left table

I am having some trouble trying to figure out how to write a sql statement that will return the same amount of records found in the left table.

For instance we have two tables, Transactions and Partners. Due to how the tables were originally designed there does not exist a way to retrieve an exact matching pair. IE. A transaction could have many partners that it relates to.

What I am looking to do is display all the transactions with a Partner ID. If a transaction has more than one matching Partner ID then I need to take the first occurrence of the match and throw away the rest. If a transaction does not have a matching Partner ID, I still need to display it, but with an empty or null value for the Partner ID.

Transaction Table

Transaction ID | ID 1 | ID 2
-------------- +---------+----------
T1 | A | 1
T2 | C | 3
T3 | B | 1
T4 | D | 4
T5 | A | 2


Partner Table

Transaction ID | ID 1 | ID 2
---------------+---------+----------
P1 | A | 1
P2 | B | 2
P3 | C | 3
P4 | C | 3
P5 | D | 4


Desired Results

Transaction ID| ID 1 | ID 2 | Partner ID
--------------+---------+----------+-----------
T1 | A | 1 | P1
T2 | C | 3 | P3
T3 | B | 1 | Null
T4 | D | 4 | P5
T5 | A | 2 | Null


I feel like I need some form of outer join to make sure no transactions are not queried, but I cant decipher how to make sure no duplicate transactions are displayed.

Thanks

Answer

SQL tables have no concept of "first" record, without some column to specify the ordering. But, the basic idea of what you want uses left join and row_number():

select t.*, p.partnerid
from transaction t left join
     (select p.*,
             row_number() over (partition by id1, id2 order by partnerid) as seqnum
      from partner p
     ) p
     on t.id1 = p.id1 and t.id2 = p.id2 and p.seqnum = 1;

This version takes "first" to mean "lowest value of partner id".