SQL challenge

I need to create blended benchmarks which are made up of components and weights. ( details here are not important ) If a blend is already in the system, I want to retrieve its benchmarkID. If not, I'm going to create a set of new records that make up the blend. There may be many blends that use the same components, but with differing weights. Given the following array, what do you suppose is the most efficient/elegant way to find a match? A single sql query would be ideal, but using qb.get() (or queryexecute() returning an array of structs) and arrayEach() is acceptable too.

//  the table name is benchmarkBlends and the rows we're trying to match look like this:

[
	{
		"benchmarkID": 46,
		"componentID": 11,
		"pctWeight": 52.2
	},
	{
		"benchmarkID": 46,
		"componentID": 13,
		"pctWeight": 20.0
	},
	{
		"benchmarkID": 46,
		"componentID": 22,
		"pctWeight": 27.8
	}
]

// the incoming blend looks like this:
[
	{
		"componentID": 11,
		"pctWeight": 52.2
	},
	{
		"componentID": 13,
		"pctWeight": 20.0
	},
	{
		"componentID": 22,
		"pctWeight": 27.8
	}
]

Are you using SQL Server if so I would put the incoming values into a table variable... and since the assumption that all the components sum up to 100 then if you do a select and there is no match then you would insert into whatever table has your benchmarkID and then use that to insert the components....

DECLARE @benchmarks as table
--CREATE TABLE @benchmarks
 (benchmarkID int, label varchar(max))
DECLARE @benchmarkBlends as table
---CREATE TABLE @benchmarkBlends
    (benchmarkID int, componentID int, pctWeight float);

INSERT INTO @benchmarks (benchmarkid, label)  VALUES (46, 'label')

INSERT INTO @benchmarkBlends
    (benchmarkID, componentID, pctWeight)
VALUES
 (46, 11, 52.2),
 (46, 13, 20.0),
 (46, 22, 27.8)


DECLARE @tmp as table(componentID int, pctWeight float)
INSERT INTO @tmp (componentID, pctWeight) VALUES (11, 53.2), (13, 19.0), (22, 27.8)
DECLARE @benchmarkID as int
DECLARE @tot as float
SELECT @tot = sum(t.pctWeight), @benchMarkID = max(bb.benchmarkID) FROM @tmp t inner join @benchmarkBlends bb on t.componentID = bb.componentID and t.pctWeight = bb.pctWeight

if ( @tot != 100 ) 
BEGIN
	INSERT INTO @benchmarks (label) VALUES ('NewLabel')
    SET @benchmarkID = @@IDENTITY
	INSERT INTO @benchMarkBlends (benchmarkID, componentID, pctWeight)
    SELECT @benchmarkID, t.componentID, t.pctWeight FROM @tmp t
   
END

PRINT @benchmarkID
SELECT * FROM @benchmarkBlends

@Michael Schmidt interesting solution. The problem is that blends could use the same combination of benchmarks but with differing weights.

That solutions hould cover that... that is why the @tmp coming in is of different then what is stored...

tricky problem to solve

to prove that it works for both cases

I see

Reuse part of Michael's code, to find a match, we could also use GROUP BY

DECLARE @benchmarkBlends as table (benchmarkID int, componentID int, pctWeight float);

INSERT INTO @benchmarkBlends
    (benchmarkID, componentID, pctWeight)
VALUES
 (46, 11, 52.2),
 (46, 13, 20.0),
 (46, 22, 27.8),
 (47, 10, 50.2),
 (47, 9, 25.0),
 (47, 8, 24.8),
 (48, 12, 50.0),
 (48, 15, 25.0),
 (48, 16, 25.0),
 (50, 11, 33.3),
 (50, 13, 33.3),
 (50, 29, 33.4)

SELECT benchmarkID
FROM @benchmarkBlends
WHERE
	(componentID = 11 AND pctWeight = 52.2) OR 
	(componentID = 13 AND pctWeight = 20.0) OR 
	(componentID = 22 AND pctWeight = 27.8)
GROUP BY
	benchmarkID
Having
	sum(pctWeight) = 100

If there is data return from the SQL, then there is a match, if there are more than one records return, then there are multiple matches. Borrowed Michael's idea, just a different SQL.

bit late, you could generate a json representation for the blend makeup and use that for the check