Question

Join tables with condition that all row values are unique

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.

 3  88  3
1 Jan 1970

Solution

 5

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

2024-07-10
Squirrel