SQL Join Based on Order of Occurrence on First Match that Hasn't Already Been Matched

user_2636234 0
Viewed 53

Lets say I have 2 tables - Table 1 and Table 2:

enter image description hereenter image description here

I want to be able to go down the in the order that the two tables are presented in and match without duplication. So for this example:

  1. Look at Table 1, ID 1 and match to first type match with Table 2. In this case, Type in Table 1 for ID 1 is A. So in Table 2 look through in the order that it is presented, and get the first color that has type A that hasn't already been matched. In this case that would be "red"
  2. Look at Table 1, ID 2 and match to first type match with Table 2. In this case, Type in Table 1 for ID 2 is B. So in Table 2 look through in the order that it is presented, and get the first color that has type B that hasn't already been matched. In this case that would be "green"
  3. Look at Table 1, ID 3 and match to first type match with Table 2. In this case, Type in Table 1 for ID 3 is A again. So in Table 2 look through in the order that it is presented, and get the first color that has type A that hasn't already been matched. In this case that would be "yellow"
  4. Look at Table 1, ID 4 and match to first type match with Table 2. In this case, Type in Table 1 for ID 4 is C. So in Table 2 look through in the order that it is presented, and get the first color that has type C that hasn't already been matched. In this case that would be "blue"
  5. Look at Table 1, ID 5 and match to first type match with Table 2. In this case, Type in Table 1 for ID 5 is B again. So in Table 2 look through in the order that it is presented, and get the first color that has type B that hasn't already been matched. In this case all Type Bs have already been matched, so return "Null"

So the results table would look something like this:

enter image description here

How would one do this is SQL? Is there a type of "JOIN" that would do this? Is it possible to have for-loops in SQL where you find the match from Table 2 and remove that instance from Table 2 in time for the next iteration of the loop?

sql-server sql
1 Answers

Use row numbers:

select t1.*, t2.*
from (select t1.*, row_number() over (partition by type order by id) as seqnum
      from table1 t1
     ) t1 left join
     (select t2.*, row_number() over (partition by type order by (select null)) as seqnum
      from table1 t2
     ) t2 
     on t1.type = t2.type and t1.seqnum = t2.seqnum;

Note that SQL tables represent unordered sets. You have no obvious ordering in table2, so no ordering is guaranteed in the results.

user_11440350
Related