Mysql left join and get different data

Question

I would like to get a different record from the left join sql.

SELECT sd.bill_amount, mr.payment_amount 
FROM event_mgmt_settlement_details sd 
LEFT JOIN event_mgmt_registration mr 
ON sd.order_id = mr.order_id

In this sql, I can get all the records from 2 tables with same order_id, but some sd.order_id or mr.order_id that have unique id that each other don't have, how I can get those records?

Answer 1

SELECT sd.bill_amount, mr.payment_amount 
FROM event_mgmt_settlement_details sd , event_mgmt_registration mr

Answer 2

try this:

SELECT sd.bill_amount, mr.payment_amount 
FROM event_mgmt_settlement_details sd ,event_mgmt_registration mr 

so if you have sd.order_id have 1 to 11 and mr.order_id have 1 to 10 the result is:

1 : 1, 2 : 1, 3 : 1, etc .. 11 : 10

Answer 3

One way to find the unmatch record from both table is to do a union all and then select data with group_by. This way works well if the tables are having the same column names. Since you have different names then it needs some adjustment and perhaps better to make an identity to see from which table the record is coming.

select 
order_id,
amount,
table_name
from
(
  select 
  order_id,
  bill_amount as amount,
  'Table1' as table_name
  from event_mgmt_settlement_details
  union all
  select 
  order_id,
  payment_amount as amount,
  'Table2' as table_name
  from event_mgmt_registration
)x
GROUP BY order_id HAVING COUNT(*) = 1;

DEMO

Answer 4

I think you want:

SELECT sd.order_id, sd.bill_amount, 'event_mgmt_settlement_details'
  FROM event_mgmt_settlement_details sd 
  LEFT JOIN event_mgmt_registration mr  
    ON sd.order_id = mr.order_id
 WHERE mr.order_id is null
 UNION ALL
SELECT mr.order_id, mr.payment_amount, 'event_mgmt_registration'
  FROM event_mgmt_settlement_details sd 
 RIGHT JOIN event_mgmt_registration mr  
    ON sd.order_id = mr.order_id
 WHERE sd.order_id is null

Since MySql doesn't support full outer join, union left join and right join instead.

Answer 5

you can consider the following schema for different join types:

SELECT sd.bill_amount, mr.payment_amount 
FROM event_mgmt_settlement_details sd 
FULL OUTER JOIN event_mgmt_registration mr 
ON sd.order_id = mr.order_id
WHERE sd.order_id IS NULL OR mr.order_id IS NULL;