Whats wrong with this query? i am getting null result

Question

SELECT `acart`.`order_number` AS `admin_order_number`, 
       `acart`.`user_id` AS `admin_user_id`, 
       `acart`.`created_by` AS `admin_created_by`, 
       `rcart`.`order_number` AS `renew_order_number`, 
       `rcart`.`user_id` AS `renew_user_id`, 
       `rcart`.`created_by` AS `renew_created_by`, 
       `scart`.`order_number` AS `shopping_order_number`,
       `scart`.`user_id` AS `shopping_user_id`,
       `scart`.`created_by` AS `shopping_created_by` 
FROM `cdp_order_transaction_master` AS `master`
LEFT JOIN `cdp_admin_shopping_cart` AS `acart` 
    ON `acart`.`order_number`=`master`.`order_number` 
LEFT JOIN `cdp_renew_cart` AS `rcart` 
    ON `rcart`.`order_number`=`master`.`order_number` 
LEFT JOIN `cdp_shopping_cart` AS `scart` 
    ON `scart`.`order_number`=`master`.`order_number` 
WHERE master.order_number IS NULL

Let me explain my problem,if the order is successfull then it will goes to cdp_order_transaction_master table and any other 3 table (cdp_admin_shopping_cart,cdp_renew_cart,cdp_shopping_cart) depending on the situation but if the order fails then it will not go to cdp_order_transaction_master table and remain in other tables, so i want the failed order which is not present in cdp_order_transaction_master and can present in any other tables(cdp_admin_shopping_cart,cdp_renew_cart,cdp_shopping_c‌art)

please read this before posting questions: https://stackoverflow.com/help/mcve — Esteban P., Jul 03 '17 at 09:12
You sure you don't want `WHERE master.order_number IS NOT NULL` ? — Prabhat G, Jul 03 '17 at 09:19
yes i want only NULL, i edit my question for better understanding,please read it once. — stack overflow, Jul 03 '17 at 09:30

JohnHC · Answer 1 · 2017-07-03T09:24:27.803

2

WHERE master.order_number IS NULL

This is your primary table, and the join condition for all other tables are on this column.

You are trying to join null to null

Based on your comment, try:

select 'cdp_admin_shopping_cart' as `err_table`, a1.order_number
from cdp_admin_shopping_cart a1
where not exists (select 1 from cdp_order_transaction_master a2 where a2.order_number = a1.order_number)

union all

select 'cdp_renew_cart' as `err_table`, a1.order_number
from cdp_renew_cart a1
where not exists (select 1 from cdp_order_transaction_master a2 where a2.order_number = a1.order_number)

union all

select 'cdp_shopping_c‌art' as `err_table`, a1.order_number
from cdp_shopping_c‌art a1
where not exists (select 1 from cdp_order_transaction_master a2 where a2.order_number = a1.order_number)

edited Jul 03 '17 at 09:24

answered Jul 03 '17 at 09:15

JohnHC

10,935
1
24
40

let me explain my problem,if the order is successfull then it will goes to cdp_order_transaction_master table and any other 3 table (cdp_admin_shopping_cart,cdp_renew_cart,cdp_shopping_cart) depending on the situation but if the order fail then it will not go to cdp_order_transaction_master table and remain in other tables – stack overflow Jul 03 '17 at 09:20
so i want the failed order which is not present in cdp_order_transaction_master and can present in any other tables(cdp_admin_shopping_cart,cdp_renew_cart,cdp_shopping_cart) – stack overflow Jul 03 '17 at 09:21

Whats wrong with this query? i am getting null result

1 Answers1