CODEWITHSUNDEEP
mysql
GenXeral
GenXeral

Reputation: 151

Duplicating values in a column for all rows in a given id

I have a table of Ids with "orders" and "shipment" timestamps. Most Ids will have both "orders" and "shipment" rows, but some Ids will only have shipment. I want to implement a If-Then Logic where if I see an order row, then retrieve the timestamp. If an Id does not contain an Order row, then I will resort to retrieving the "shipment" timestamp.

One way I was trying to solve this was creating a case statement to create a new 'Orders' column and populate that specific timestamp for each Orders and Shipment Row. I'll create a subquery where if Orders column is not null, then use that timestamp. Or if Orders column is null, then use the shipment timestamp.

I can't simply just filter by rows = "orders" because it'll eliminate the IDs that only contains shipments

Original Table:

id    Type     Date    
1     orders    3/28     
1     shipment    3/23

Expected Result:

id    Type       Date    Orders_Column
1     orders      3/28    3/28
1     shipment     3/23   3/28

Upvotes: 0

Views: 33

Answers (1)

Nick
Nick

Reputation: 147206

You can do this with a LEFT JOIN of the table to itself, looking for a matching orders row for the id. If there isn't a matching row, the shipment date is used:

SELECT o1.*, COALESCE(o2.Date, o1.Date) AS `Date`
FROM orders o1
LEFT JOIN orders o2 ON o2.id = o1.id AND o2.Type = 'orders'

Output (for a demo with an extra shipment only id):

id  Type        Date    Date
1   orders      3/28    3/28
1   shipment    3/23    3/28
2   shipment    3/25    3/25

Demo on dbfiddle

Upvotes: 2

Related Questions