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.