SQL Query - Max Value

holemania
holemania used Ask the Experts™
on
I need help with a query.  I have a nested query that pull from 2 tables with a union all.  I want to combine the 2 record it pull, but the greater value of the 2 into 1 record.  Or more like if it's NULL then use the other value from the other record.

See my query and example output.
SELECT 	X.ID, X.ORDER_ID, X.DATE, X.STATUS, X.ORDER_POLICY
FROM	(
	SELECT 	ITEM_ID AS ID, ORDER_ID, TRANSACTION_DATE AS DATE, NULL AS STATUS, AS NULL ORDER_POLICY
	FROM	SALES_ORDER
	WHERE	
	UNION ALL
	SELECT	ID, NULL AS ORDER_ID, NULL AS DATE, NULL AS STATUS, ORDER_POLICY
	FROM	ITEMS
	) X
WHERE	X.ID = '129892' AND ISNULL(NULLIF(X.ORDER_ID, ''), '' = 'SO12342'

Open in new window

output-sample.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
SELECT   x.id, MAX(x.order_id), MAX(x.date), MAX(x.status), MAX(x.order_policy)
    FROM (SELECT item_id AS id,
                 order_id,
                 transaction_date AS date,
                 NULL AS status,
                 NULL AS order_policy
            FROM sales_order
            where
          UNION ALL
          SELECT id, NULL AS order_id, NULL AS date, NULL AS status, order_policy FROM items) x
GROUP BY id
  HAVING x.id = '129892' AND x.order_id = 'SO12342'
Most Valuable Expert 2011
Top Expert 2012
Commented:
another way, I'm not sure where status comes from though


SELECT item_id AS id,
       order_id,
       transaction_date AS date,
       NULL AS status,
       (SELECT order_policy
          FROM items
         WHERE items.id = sales_order.item_id)
           AS order_policy
  FROM sales_order
 WHERE item_id = '129892' AND order_id = 'SO12342'

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial