select
  ProductCodes.product_id
  ,ProductCodes.region
  ,ProductCodes.countrycode
  ,ProductCodes.fullcode
  ,ProductNames.product_name
  ,LCR.quality
  ,LCR.min_rate
  ,max(LCR.quality) as quality
  ,min(LCR.min_rate) as minRate
from
  ProductCodes
  ,ProductNames
    left outer join LCR on ProductCodes.product_id = LCR.product_id
where
  ProductCodes.product_id = ProductNames.product_id
  and ProductCodes.fullcode not in ('0','9370','9379')
  and ProductCodes.region=1
  and ProductCodes.countrycode=1684
  and LCR.min_rate > 0
group by
   ProductNames.product_name;
order by
   LCR.quality
   ,ProductNames.product_name

select ProductCodes.product_id
     , ProductCodes.region
     , ProductCodes.countrycode
     , ProductCodes.fullcode
     , ProductNames.product_name
     , max(LCR.quality)       as maxquality
     , min(LCR.min_rate)      as minRate
  from ProductCodes
inner
  join ProductNames
    on ProductNames.product_id = ProductCodes.product_id
left outer 
  join LCR 
    on LCR.product_id = ProductCodes.product_id
   and LCR.min_rate > 0
 where ProductCodes.fullcode not in ('0','9370','9379')
   and ProductCodes.region=1
   and ProductCodes.countrycode=1684
group 
    by ProductCodes.product_id
order 
    by maxquality
     , ProductNames.product_name

CREATE TABLE LCR (
 product_id INT UNSIGNED NOT NULL,
 min_rate FLOAT,
 quality INT
);

CREATE TABLE ProductNames (
 product_id INT UNSIGNED NOT NULL,
 product_name VARCHAR(255)
);

CREATE TABLE ProductCodes (
 product_id INT UNSIGNED NOT NULL,
 region INT,
 countrycode INT,
 fullcode INT
);

INSERT INTO LCR VALUES(455,0.00900,0),
(456,0.06450,2),(457,0.05950,2),
(458,0.01210,0),(459,0.01200,0);

INSERT INTO ProductNames VALUES(455,'Tires'),
(456,'Wheels'),(456,'Jacks'),(458,'Hammer'),(459,'Chocks');

INSERT INTO ProductCodes VALUES(456,1,93,93),
(458,1,93,9379),(455,1,93,9320),(459,1,93,9320),(458,1,355,3554);