SELECT all orders data including matched and unmatched orders SQL-CodePudding

I need to select all orders data including orders which lead to a transaction those which didn't lead to a transaction.

Knowing that:

SELECT * FROM Buy_Orders

OrderID   OrderQuantity   OrderPrice      OrderPlacementDate   
-----------------------------------------------------------------
12        11              103             2021-10-12 14:02:22.703
14        6               100             2021-10-12 14:04:24.700
14        0               100             2021-10-12 14:07:27.206
17        3               80              2021-10-12 14:08:22.703
12        0               103             2021-10-12 14:09:21.501
20        20              23              2021-10-12 14:11:23.705

SELECT * FROM Sell_Orders

OrderID   OrderQuantity   OrderPrice   OrderPlacementDate   
--------------------------------------------------------------
9         2               13           2021-10-12 14:05:25.705
23        7               100          2021-10-12 14:07:27.205
23        1               100          2021-10-12 14:07:27.206
33        9               90           2021-10-12 14:08:28.403
90        1               103          2021-10-12 14:09:21.500
90        0               103          2021-10-12 14:09:21.501

SELECT * FROM Transactions

TransactionID    TransactionQuantity    TransactionPrice    SellOrderID    BuyOrderID       
---------------------------------------------------------------------------------------
113              6                      100                  23             14     
123              1                      103                  90             12

Logic for TransactionID 113 ( SellOrderID 23 BuyOrderID 14 ): transaction created when order 23 entered the order book at 2021-10-12 14:07:27.205 and matched order 14 (partial fill). That's why there was an update on both impacted orders (23 & 14) at 2021-10-12 14:07:27.206 in tables Sell_Orders and Buy_Orders. So, the match with quantity=6 will create an update on order 23 to re-enter the orderbook with quantity=1 and an update on order 14 to re-enter the order book with quantity=0 at 2021-10-12 14:07:27.206.

I have tried the following SQL query but with no chance. I assume I'm not fluent with SQL. Please help!

SELECT 
    o.OrderID
    o.OrderQuantity
    o.OrderPlacementDate
    t.TransactionID 
FROM (
    SELECT * 
from 
       Sell_Orders 
UNION 
SELECT *
from 
      Buy_Orders ) o
LEFT JOIN (
    SELECT 
      TransactioID 
    FROM 
      Transactions ) t on t.SellOrderID = o.OrderID or t.BuyOrderID = o.OrderID

I expect to have this table as an output:

OrderID   TransactionID   OrderQuantity   OrderPrice   OrderPlacementDate
---------------------------------------------------------------------------
12        NULL            1               103          2021-10-12 14:02:22.703  
14        NULL            6               100          2021-10-12 14:04:24.700
9         NULL            2               13           2021-10-12 14:05:25.705
23        NULL            7               100          2021-10-12 14:07:27.205 -----> 1st Transaction
23        113             1               100          2021-10-12 14:07:27.206
14        113             0               100          2021-10-12 14:07:27.206
17        NULL            3               80           2021-10-12 14:08:22.703  
33        NULL            9               90           2021-10-12 14:08:28.403
90        NULL            1               103          2021-10-12 14:09:21.500 -----> 2nd Transaction
90        123             0               103          2021-10-12 14:09:21.501
12        123             0               103          2021-10-12 14:09:21.501
20        NULL            20              23           2021-10-12 14:11:23.705

CodePudding user response：

Your last subquery doesn't include the order id's, so there's nothing to join t on. Just don't use a subquery.

LEFT JOIN (
    SELECT 
      TransactioID 
    FROM 
      Transactions ) t on t.SellOrderID = o.OrderID or t.BuyOrderID = o.OrderID

Becomes...

LEFT JOIN 
  Transactions   t
    ON t.SellOrderID = o.OrderID
    OR t.BuyOrderID  = o.OrderID

EDIT:

You also want each transaction to join on just one buy order, and one sell order, which requires adding a ranking id to each order.

Provided that (OrderID, OrderPlacementDate) is guaranteed to be unique, that can be accomplished with...

SELECT 
    o.*,
    t.TransactionID 
FROM
(
    SELECT
        *,
        'Sell' AS OrderType,
        ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY OrderPlacementDate DESC)  AS OrderIDRank
    FROM
        Sell_Orders

    UNION ALL  -- ALWAYS use ALL unless you KNOW a reason otherwise

    SELECT
        *,
        'Buy' AS OrderType,
        ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY OrderPlacementDate DESC)  AS OrderIDRank
    FROM
        Buy_Orders
)
    AS o
LEFT JOIN
    Transactions   AS t
       ON  o.OrderIDRank = 1
       AND o.OrderID    IN (t.BuyOrderID, t.SellOrderID)

CodePudding user response：

This query gets expected table

with All_Orders as( 
SELECT *,'S' as side from  Sell_Orders 
UNION ALL
SELECT *,'B' as side from Buy_Orders 
)
SELECT 
    o.OrderID
   ,case when TransactionQuantity<=OrderQuantity then null
          else TransactionId
     end TransactionId
   ,o.OrderQuantity
   ,o.OrderPlacementDate
    -- for info
   ,t.TransactionID as realTranId
   ,o.Side,TransactionQuantity
FROM All_Orders o LEFT JOIN Transactions  t on t.SellOrderID = o.OrderID or t.BuyOrderID = o.OrderID
order by OrderPlacementDate

Source row
12, 11, 103, 2021-10-12 14:02:22.703
may be ?
12, 1, 103, 2021-10-12 14:02:22.703

DBFiddle example