I have two SQL queries. I need to join them into a single SQL query-CodePudding

I have two sql queries which I need to combine into a single one. When I'm trying to combine these 2 queries into a single one, I'm not getting the right result. Someone please help in combining these 2 queries into a single query.

First SQL Query:

SELECT distinct order_no,status,status_cd,
case when order_qty - fulfilled_qty > 0 then 'inventory-unavailable' END
from t1 a, t2 b
where 
a.inventory_stockout='TRUE'
AND a.flag_column='TRUE' 
and status in '(fulfilled','pending')
GROUP BY order_no,status,status_cd;

Second SQL Query:

SELECT distinct order_no,status,status_cd,
case when order_qty - fulfilled_qty > 0 then 'inventory-unavailable' END
from t1 a, t2 b
where 
a.inventory_stockout='TRUE'
AND a.flag_column='TRUE' 
and a.subscription_enabled='TRUE'
GROUP BY order_no,status,status_cd;

CodePudding user response：

If both of them - separately - return result you want (I doubt it; you're using cross-join between t1 and t2), then a simple option is to union them (remove distinct, remove group by (doesn't make sense anyway as you aren't aggregating anything, but distinct does the job) as union replaces both of them in this case):

SELECT order_no,
       status,
       status_cd,
       CASE
          WHEN order_qty - fulfilled_qty > 0 THEN 'inventory-unavailable'
       END
  FROM t1 a, t2 b
 WHERE     a.inventory_stockout = 'TRUE'
       AND a.flag_column = 'TRUE'
       AND status IN ('fulfilled', 'pending')
UNION
SELECT order_no,
       status,
       status_cd,
       CASE
          WHEN order_qty - fulfilled_qty > 0 THEN 'inventory-unavailable'
       END
  FROM t1 a, t2 b
 WHERE     a.inventory_stockout = 'TRUE'
       AND a.flag_column = 'TRUE'
       AND a.subscription_enabled = 'TRUE';

CodePudding user response：

One question: I see t1 and t2 and synonyms but don't see the join condition or the key logic that joins t1 and t2 together. Do you know what the PK/FK relationship is between those tables?

Suggestion: You're hitting the same table in both with most of the same conditions and same case logic. So, if you combined into a single query, you'd make one pass through the tables which would probably be faster than a UNION. If you add some () to the AND logic, would enable you to do this. Can you try this style and see if it works?

-- Combined Code Mockup

SELECT distinct order_no,status,status_cd,
  CASE when order_qty - fulfilled_qty > 0 then 'inventory-unavailable' END
  FROM t1 a, t2 b
 WHERE t1.PK = t2.FK (Add...)
   AND a.inventory_stockout='TRUE'
   AND a.flag_column='TRUE' 
   AND (status in '(fulfilled','pending' OR (AND a.flag_column='TRUE' 
        AND a.subscription_enabled='TRUE'))
GROUP BY order_no,status,status_cd;