365 Days of SQL: Day 149

The rain has finally stopped after continuously raining for the past 2 days. There’s been flooding in several places in KL. I was actually invited to a friend’s house but i did not go because it was raining. It was wise decision although I felt a bit bad for not going.

Puzzle #34: Specific Exlcusion

I tried the solution in the oracle environment here.

WITH temp1 AS (
SELECT 1001 AS CustomerID, 'Ord143933' AS OrderId, 25 AS Amount FROM DUAL UNION ALL
SELECT 1001 AS CustomerID, 'Ord789765' AS OrderId, 50 AS Amount FROM DUAL UNION ALL
SELECT 2002 AS CustomerID, 'Ord345434' AS OrderId, 65 AS Amount FROM DUAL UNION ALL
SELECT 3003 AS CustomerID, 'Ord785633' AS OrderId, 50 AS Amount FROM DUAL
), temp2 AS (
SELECT OrderID FROM temp1
WHERE CustomerID = '1001' AND Amount = 50)

SELECT * FROM temp1
WHERE OrderID NOT IN (SELECT * FROM temp2);