0
SELECT
      A.update_date,
      A.create_user,
      A.service_id,A.order_id,
      '10009' AS main_plan_id,
      'Prepaid Cash' AS main_plan_name,
      ' ' AS topping_plan_id,
      ' ' AS topping_plan_name,
      '0' AS Price
FROM 
      ORDER_MASTER A 
WHERE 
      A.pending_order_type = 'Sim delivery' 
AND
      A.order_status='Pending' 
AND
      DATEDIFF(NOW(),update_date) > 30
GROUP BY 
      A.update_date,
      A.create_user,
      A.service_id,
      A.order_id,
      main_plan_id,
      main_plan_name,
      topping_plan_id,
      topping_plan_name;

SELECT
      A.update_date,
      A.create_user,
      A.service_id,
      A.order_id,
      ' ' AS main_plan_id,
      ' ' AS main_plan_name,
      D.topping_plan_id,
      D.topping_plan_name,
      D.charge_amount AS Price 
FROM 
      ORDER_MASTER A
LEFT JOIN
         (
           SELECT 
                 B.service_id AS service_id,
                 B.plan_id AS topping_plan_id,
                 B.plan_name AS topping_plan_name,
                 C.charge_amount charge_amount
           FROM 
                 BILLING.BS_SERVICE_SUBSCRIPTION_MASTER B,
                 BILLING.BS_SUBSCRIPTION_CHARGE_DETAILS C
           WHERE 
                 B.subscription_id = C.subscription_id
         ) D
ON 
      A.service_id = D.service_id 
WHERE 
      A.pending_order_type='Sim delivery' 
AND
      A.order_status='Pending' 
AND
      DATEDIFF(NOW(),update_date) > 30 
GROUP BY
      A.update_date,
      A.create_user,
      A.service_id,
      A.order_id,
      main_plan_id,
      main_plan_name,
      D.topping_plan_id,
      D.topping_plan_name;
3
  • Formatting the code makes it much easier to understand, for reader as well as writer. What is the expected output of this join, what join predicate should be used etc? Mar 31, 2020 at 9:28
  • In the first query I am printing main plan name,id,amount for a particular order and in the second query I am printing topping plan name,id,amount for that same order(say,a sub plan). So for each order I want the main plan details to be printed in one row and then the sub plan details in the next row.
    – user205366
    Mar 31, 2020 at 9:46
  • How does your second query select the same order as the first one? Mar 31, 2020 at 18:16

1 Answer 1

1

Is this what you need?

( SELECT ... )
UNION ALL
( SELECT ... )

The GROUP BY on the first SELECT seems to be useless; please explain why you have it. I am less certain about the second query.

Not sargable: DATEDIFF(NOW(),update_date) > 30; change to A.update_date > CURDATE() - INTERVAL 20 DAY.

Recommend:

A:  INDEX(order_status, pending_order_type, update_date)
C:  INDEX(subscription_id, charge_amount)

When JOINing, please use FROM B JOIN C ON ... instead of FROM B, C WHERE ...

(If you need further discussion, pleas provide SHOW CREATE TABLE.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.