вообщем если я правильно понял задачу, то на входе есть две таблички, необычным образом связанные по полю 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;
