I am using a SQL Server database and pulling several columns from subqueries (Invoice date, Est ship date, etc). How to replace multiple left joins with just one join and use the case
statement in the Select
statement? Or is there a better solution? Thanks in advance.
SELECT Orders.ID
, InvDate.Event_Time AS Invoice_date
, EstShipDate.Event_Time AS Est_Ship_date
, DelDate.Event_Time AS Del_date
, ReturnDate.Event_Time AS Return_date
FROM
Orders
LEFT JOIN
(SELECT EventType, OrderID, Event_Time
FROM Ship_LOG_TIMES) InvDate ON InvDate.OrderID = Order.ID
AND INvDate.EventType = '1' --Invoice date
LEFT JOIN
(SELECT EventType, OrderID, Event_Time
FROM Ship_LOG_TIMES) EstShipDate ON EstShipDate.OrderID = Order.ID
AND EstShipDate.EventType = '2' --Est Ship date
LEFT JOIN
(SELECT EventType, OrderID, Event_Time
FROM Ship_LOG_TIMES) DelDate ON DelDate.OrderID = Order.ID
AND DelDate.EventType = '4' -- Delivery date
LEFT JOIN
(SELECT EventType, OrderID, Event_Time
FROM Ship_LOG_TIMES) ReturnDate ON ReturnDate.OrderID = Order.ID
AND ReturnDate.EventType = '5' --Return date'
2条答案
按热度按时间mspsb9vt1#
You could try this query
aelbi1ox2#
I would use a sub query like so: