Made a triple left / right join to display the results of the three queries in three columns.

SELECT t1.order_number as order_t1, t2.order_number as order_t2, t3.order_number as order_t3 FROM ( SELECT order_number,`order`.order_id FROM `order` inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id where order_status = 'delivered' AND order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) and order_delivery_data_name in ('Казахстан Курьеры') ) as t1 LEFT JOIN (SELECT order_number,`order`.order_id FROM `order` inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id where order_status = 'delivered' AND order_statusUpdatedAt BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) AND DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) and order_delivery_data_name in ('Казахстан Курьеры') ) as t2 ON t1.order_id = t2.order_id LEFT JOIN (SELECT order_number,`order`.order_id FROM `order` inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id where order_status = 'delivered' AND order_statusUpdatedAt BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) AND DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) and order_delivery_data_name in ('Казахстан Курьеры') ) as t3 ON t2.order_id = t3.order_id UNION ALL SELECT t1.order_number as order_t1, t2.order_number as order_t2, t3.order_number as order_t3 FROM ( SELECT order_number,`order`.order_id FROM `order` inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id where order_status = 'delivered' AND order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) and order_delivery_data_name in ('Казахстан Курьеры') ) as t1 RIGHT JOIN (SELECT order_number,`order`.order_id FROM `order` inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id where order_status = 'delivered' AND order_statusUpdatedAt BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) AND DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) and order_delivery_data_name in ('Казахстан Курьеры') ) as t2 ON t1.order_id = t2.order_id RIGHT JOIN (SELECT order_number,`order`.order_id FROM `order` inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id where order_status = 'delivered' AND order_statusUpdatedAt BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) AND DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) and order_delivery_data_name in ('Казахстан Курьеры') ) as t3 ON t2.order_id = t3.order_id

Displays data in the first and third columns correctly, in the second one is null. If you do not join the third query, then it displays the results of the first and second columns correctly. Why can not display the result of the second column? And is it possible to remove null values ​​from all columns? UPD. If you make only two requests, then displays the data in both columns

 SELECT t1.order_number as order_t1, t2.order_number as order_t2 FROM ( SELECT order_number,`order`.order_id FROM `order` inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id where order_status = 'delivered' AND order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) and order_delivery_data_name in ('Казахстан Курьеры') ) as t1 LEFT JOIN (SELECT order_number,`order`.order_id FROM `order` inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id where order_status = 'delivered' AND order_statusUpdatedAt BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) AND DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) and order_delivery_data_name in ('Казахстан Курьеры') ) as t2 ON t1.order_id = t2.order_id UNION ALL SELECT t1.order_number as order_t1, t2.order_number as order_t2 FROM ( SELECT order_number,`order`.order_id FROM `order` inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id where order_status = 'delivered' AND order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) and order_delivery_data_name in ('Казахстан Курьеры') ) as t1 RIGHT JOIN (SELECT order_number,`order`.order_id FROM `order` inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id where order_status = 'delivered' AND order_statusUpdatedAt BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY) AND DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) and order_delivery_data_name in ('Казахстан Курьеры') ) as t2 ON t1.order_id = t2.order_id
  • If I understand the logic correctly, then you collect data on the principle “we take the last week, and add data for the last 10 days without this week and for the last 2 months without these 10 days”, right? If so, then you have skew logic, and the third subquery should be associated with the first, not the second. - Akina
  • The logic is the following, the first request is data for 7 days, the second is 8-10 days, the third is 11 days or more. if you meant it ON t1.order_id = t3.order_id , then tried, all the same the second column empty. - cruim
  • It would be better to describe what data you have in the tables and what result you want to get. Are your subqueries one by one order_id now guaranteed to give 0 or 1 record? If they suddenly can give more than one record, then the output will be nonsense from the multiplied data. Apparently, you are trying to emulate a full outer join, but it’s better to emulate using completely different methods - union, group by and horizontal expansion, and if so, then most likely your problem can be solved in a single reference to the table - Mike
  • Логика следующая - well, I said the same, in other words, just. - Akina
  • If from t1 left join t2 on t1.f1=t2.f1 gives entries for t2, and from t1 left join t2 on t1.f1=t2.f1 left join t3 on tX.f1=t3.f1 for t2 does not give them, there is a difference between requests for t1-t2. - Akina

1 answer 1

I think you will receive the necessary data using this query:

 SELECT IF(diff<7,order_number,NULL) order_t1, IF(diff>=7 and diff<=10,order_number,NULL) order_t2, IF(diff>10,order_number,NULL) order_t3 FROM ( SELECT `order`.order_id,order_number,datediff(CURRENT_DATE, order_statusUpdatedAt) diff FROM `order` inner join order_delivery_data on `order`.order_id = order_delivery_data.order_id where order_status = 'delivered' AND order_statusUpdatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) and order_delivery_data_name in ('Казахстан Курьеры') ) X
  • and only 11 lines ... thanks! - cruim