Question 362334 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Select into variables where multiple independent sums equal

I have a table called cars, and I would like to select the cars.variant into variables for three cars, where each metric column totals an independent amount.

For example, let’s say @car1 := ‘Ferrari’, @car2 := ‘Audi’, @car3 := ‘BMW’ and sum of each metric column equals 5 (Sum of metric_1 = 5, sum of metric_2 = 5, sum of metric_3 = 5).

I would expect “SF90 Spider” to be inserted into @var1, “Q8” to be inserted into @var2, and “M2 Coupe” to be inserted into @var3.

If there isn’t an exact match for the metric, can the closest be returned?

I have tried hundreds of queries, but I am no where near a working example.

Closed thread I might have found is this: https://stackoverflow.com/a/69246359, but my lack of skills prevents me from full understanding it / adjust it.

Any help would be great!

drop table if exists cars;
CREATE TABLE cars (
`id` INT NOT NULL AUTO_INCREMENT,
`cars` VARCHAR(255) NULL DEFAULT '',
`variant` VARCHAR(255) NULL DEFAULT '',
`metric_1` varchar(10) NULL DEFAULT '',
`metric_2` varchar(10) NULL DEFAULT '',
`metric_3` varchar(10) NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

insert into cars (id, cars, variant, metric_1, metric_2, metric_3)
values
insert into cars (id, cars, variant, metric_1, metric_2, metric_3) values (1, 'Ferrari', 'Roma', '1', '1', '1');
insert into cars (id, cars, variant, metric_1, metric_2, metric_3) values (2, 'Ferrari', '296 GTB', '2', '1', '1');
insert into cars (id, cars, variant, metric_1, metric_2, metric_3) values (3, 'Ferrari', 'SF90 Spider', '3', '1', '1');
insert into cars (id, cars, variant, metric_1, metric_2, metric_3) values (4, 'Audi', 'Quattro GT RS', '1', '1', '1');
insert into cars (id, cars, variant, metric_1, metric_2, metric_3) values (5, 'Audi', 'Q4', '1', '2', '1');
insert into cars (id, cars, variant, metric_1, metric_2, metric_3) values (6, 'Audi', 'Q8', '1', '3', '1');
insert into cars (id, cars, variant, metric_1, metric_2, metric_3) values (7, 'BMW', 'M8 Coupe', '1', '1', '1');
insert into cars (id, cars, variant, metric_1, metric_2, metric_3) values (8, 'BMW', 'M3 Sedan', '1', '1', '2');
insert into cars (id, cars, variant, metric_1, metric_2, metric_3) values (9, 'BMW', 'M2 Coupe', '1', '1', '3');
insert into cars (id, cars, variant, metric_1, metric_2, metric_3) values (10, 'Maserati', 'GranTurismo', '1', '1', '1');
insert into cars (id, cars, variant, metric_1, metric_2, metric_3) values (11, 'Maserati', 'Levante', '2', '1', '1');
insert into cars (id, cars, variant, metric_1, metric_2, metric_3) values (12, 'Maserati', 'MC20', '3', '1', '1');
insert into cars (id, cars, variant, metric_1, metric_2, metric_3) values (13, 'Porsche', '718', '1', '1', '1');
insert into cars (id, cars, variant, metric_1, metric_2, metric_3) values (14, 'Porsche', 'Macan', '1', '2', '1');
insert into cars (id, cars, variant, metric_1, metric_2, metric_3) values (15, 'Porsche', '911', '1', '3', '1');
Tags:

2

Answers


  1. 0

    This seems to work:

    select 
  c1.variant,
  c2.variant,
  c3.variant
into @var1, @var2, @var3
from cars c1 
left join cars c2 on c2.cars='Audi'
                and c2.metric_1 + c2.metric_2 + c2.metric_3 = 5
left join cars c3 on c3.cars='BMW'
                and c3.metric_1 + c3.metric_2 + c3.metric_3 = 5
where c1.Cars='Ferrari' and c1.metric_1 + c1.metric_2 + c1.metric_3 = 5;


select @var1, @var2, @var3;

    I think no explanation is needed, because this is very basic SQL syntax….

    see: DBFIDDLE

    Login or Signup to reply.
  2. 0 
    SELECT CASE cars WHEN @car1 THEN @var1 := variant
                 WHEN @car2 THEN @var2 := variant
                 WHEN @car3 THEN @var3 := variant
                 END variant
FROM cars
WHERE metric_1 + metric_2 + metric_3 = 5;

    fiddle

    Of course the query output should be ignored. Also WHERE cars IN (@car1, @car2, @car3) can be added.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search