MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Items (`code` INTEGER, `value` INTEGER);

INSERT INTO Items (`code`, `value`) VALUES
  ('1', '6'),
  ('2', '8');

CREATE TABLE Locations (`code` INTEGER, `min` INTEGER, `max` INTEGER, `location` VARCHAR(4));

INSERT INTO Locations (`code`, `min`, `max`, `location`) VALUES
  ('1', '5', '8', 'loc1'),
  ('1', '4', '9', 'loc2'),
  ('2', '6', '10', 'loc3');
SELECT i.code, i.value,
       (SELECT l.location 
        FROM Locations l 
        WHERE l.code = i.code 
        ORDER BY l.max - l.min DESC LIMIT 1) location
FROM Items i


code
value
location


1
6
loc2

2
8
loc3


SELECT i.code, i.value, l.location
FROM Items i 
LEFT JOIN (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY code ORDER BY max - min DESC) rn 
  FROM Locations  
) l ON l.code = i.code AND l.rn = 1


code
value
location


1
6
loc2

2
8
loc3