365 Days of Daily Coding: Day 120

I woke up quite early today and felt like I had to be productive. So I am completing this challenge very early in the day unlike most of the time. hehe! I hope those reading this blog posts out there, please know that the beginning is the only difficult journey. There will always be light at the end of the tunnel.

Puzzle 4: Two Predicates

I tried to come up with a solution using Oracle’s live environment here.

My solution:

WITH temp AS (
Select 10001 AS CustomerID, 'Ord936254' AS OrderId, 'CA' AS DeliveryState, 340 AS Amount FROM Dual Union All
Select 10001 AS CustomerID, 'Ord143876' AS OrderId, 'TX' AS DeliveryState, 950 AS Amount FROM Dual Union All
Select 10001 AS CustomerID, 'Ord654876' AS OrderId, 'TX' AS DeliveryState, 670 AS Amount FROM Dual Union All
Select 10001 AS CustomerID, 'Ord814356' AS OrderId, 'TX' AS DeliveryState, 860 AS Amount FROM Dual Union All
Select 20002 AS CustomerID, 'Ord342176' AS OrderId, 'WA' AS DeliveryState, 320 AS Amount FROM Dual Union All
Select 30003 AS CustomerID, 'Ord265789' AS OrderId, 'CA' AS DeliveryState, 650 AS Amount FROM Dual Union All
Select 30003 AS CustomerID, 'Ord387654' AS OrderId, 'CA' AS DeliveryState, 830 AS Amount FROM Dual Union All
Select 40004 AS CustomerID, 'Ord476126' AS OrderId, 'TX' AS DeliveryState, 120 AS Amount FROM Dual 
)
Select CustomerID, OrderId, Delv2, Amount
FROM (
Select t1.CustomerID, t1.DeliveryState AS Delv1, t2.OrderId, t2.DeliveryState AS Delv2, t2.Amount
FROM temp t1 INNER JOIN temp t2
ON t1.CustomerID = t2.CustomerID)
WHERE Delv1 = 'CA' AND Delv2 = 'TX';