Join tables with condition that all row values are unique

Question

I have tables Machine and Inventory. In table Machine I have last running inventory properties. In table Inventory, I have new inventory id and properties (Not yet run in machine).

I have to return a list of possible combination inventory that can run in machines based on it last running inventory. And I also need to return list of machines with no duplicates inventory.

For list of possible combination inventory that can run in machines based on it last running inventory, I tried using CROSS JOIN and it works [Refer 1].

But for list of machines with no duplicates inventory I have no clue how to do it [Refer 2]?

Machine table - machine with last running inventory:

Machine	Inventory	Color	Size
A	1111	Red	Small
B	1112	Red	Small
C	1113	Green	Large

Inventory table - new inventory (not yet run in machines):

Inventory	Color	Size
1114	Red	Small
1115	Red	Small
1116	Green	Large

I need to get every combination (Machine CROSS JOIN Inventory where Color and Size matches)

SELECT 
    m.Machine, i.Inventory, m.Color, m.Size
FROM 
    Machine m
CROSS JOIN 
    Inventory i
WHERE 
    i.Color = m.Color AND i.Size = m.Size

Machine	Inventory	Color	Size
A	1114	Red	Small
A	1115	Red	Small
B	1114	Red	Small
B	1115	Red	Small
C	1116	Green	Large

I need to get no duplicate inventory (1 Machine, 1 Inventory)

Machine	Inventory	Color	Size
A	1114	Red	Small
B	1115	Red	Small
C	1116	Green	Large

For [2] I tried to do in the code, loop the machines and find 1 by 1 based on the color and size in Inventory table and assign.

Then exclude the one I already assign for the next machine.

But it took some time to iterate all.

Answer 1

Looks like what you want is not really every combination but just assignment of 1 machine to 1 inventory.

You can use row_number() to generate the sequence number and then use that to join.

select m.machine, i.inventory
from   (
           select machine, color, size, 
                  rn = row_number() over (partition by color, size 
                                              order by machine)
           from   Machine
       ) m
       inner join
       (
           select inventory, color, size, 
                  rn = row_number() over (partition by color, size 
                                              order by inventory)
           from   Inventory
       ) i  on  m.color = i.color 
            and m.size  = i.size
            and m.rn    = i.rn

GuidoG has kindly provided a demo fiddle