Кирилл Парасотченко

вообщем если я правильно понял задачу, то на входе есть две таблички, необычным образом связанные по полю number.

задача в том, чтобы сформировать sql-запрос

дамп базы, на которой я тестил:

drop database if exists one_and_two;
create database one_and_two;

use one_and_two;

drop table if exists one;
create table one(
	load_id int unsigned not null,
	car_in int unsigned not null,
	car_out int unsigned not null,
	unique key(load_id)
);

drop table if exists two;
create table two(
	car_id int unsigned not null,
	number int unsigned not null
);


insert into one(load_id,car_in,car_out) values
	(1,22,23),
	(2,31,44),
	(3,5,11),
	(4,7,12);
	
insert into two(car_id,number) values
	(100,78),
	(100,77),
	(101,70),
	(101,57),
	(102,97),
	(102,91),
	(103,96),
	(103,91);

sql-запрос:

select t1.load_id, t1.number as number1, t2.number as number2
from(
	select o.load_id, t.car_id - o.car_in as number
	from one as o, two as t
	where t.number = t.car_id - o.car_in
) as t1,
(
	select o.load_id, t.car_id - o.car_out as number
	from one as o, two as t
	where t.number = t.car_id - o.car_out
) as t2
where t1.load_id = t2.load_id
order by t1.load_id;