MySql Union Immediate Merge to Parent table ID

Question

Thanks in advance.

Am trying to merge two table into one. The below query returns the result that i want. Am aware UNION ALL will combine more than one tables.

But i want to merge two table rows immediately after its parent row.

SELECT 
  temp.transactionId, 
  temp.amount, 
  temp.transactionType 
FROM 
  (
   (SELECT 
      sale.transaction_id AS transactionId, 
      sale.amount AS amount, 
      'SALE' AS transactionType 
    FROM 
     t_sales sale
    WHERE 
      sale.merchant_id = '1' 
   )

   UNION ALL

   (SELECT 
      refund.refund_transaction_id AS transactionId, 
      refund.amount AS amount, 
      'REFUND' AS transactionType 
   FROM 
      t_refunds refund  
   WHERE 
      refund.merchant_id = '1' 
   )
) 
AS temp

The above query returns all refunds rows after end of the sales rows. But i need to display all the refund rows immediate after each sales rows. refunds table has sales tables ID (Primary Key). Is there any way to achieve this?

score 1 · Answer 1 · answered Apr 08 '16 at 05:57

This can be done by order by on id/sale_id.

Try this:

SELECT 
  temp.transactionId, 
  temp.amount, 
  temp.transactionType 
FROM 
  (
   (SELECT 
      sale.transaction_id AS transactionId, 
      sale.amount AS amount, 
      'SALE' AS transactionType,
      id,
      1
    FROM 
     t_sales sale
    WHERE 
      sale.merchant_id = '1' 
   )

   UNION ALL

   (SELECT 
      refund.refund_transaction_id AS transactionId, 
      refund.amount AS amount, 
      'REFUND' AS transactionType,
      sale_id as id,
      2
   FROM 
      t_refunds refund  
   WHERE 
      refund.merchant_id = '1' 
   )
   ORDER BY 4,5
) 
AS temp

MySql Union Immediate Merge to Parent table ID

1 Answers1