Avatar of dbaSQL
dbaSQL
Flag for United States of America

asked on 

real quick problem with a join (urgent)

three tables:  ordertable, canceltable, markingtable
The ordertable contains different message types:  O, A, T
The canceltable contains different message types:  X, C

Where 'O' is an order message, 'T' is a trade message. ('A' is an ack).
The only guaranteed message in all cases is the 'O' message.  (it i the origination of the order)

The unique value in all three tables is OrderNo.

I am joining the ordertable to itself on OrderNo, in order to present the details of both the O messages and the T messages. That is then joined to the marking table, to retrieve varied details about the marking of each message.

For the most part, it seems correct, but it has come to my attention that my resulting data is missing records.  I'm looking at one orderno now, for example, and it exists in the ordertable 13 times  --  one O, one A, and eleven T's (eleven trades).

My output, however, is only including four of those T messages.  

This is very urgent.  Does anybody see it?
SELECT 
o.Exchange,o.Symbol,t.Quantity [FQuantity],o.Quantity [OQuantity],o.Price,om.OMarking,
o.OrderNo,o.EventTime [OrderTime],t.EventTime [ExecutionTime],om.FPosition,c.EventTime [CancelTime]
FROM
  dbo.ordertable o LEFT JOIN dbo.ordertable t ON o.OrderNo = t.OrderNo AND o.messageType = 'O' AND t.messageType = 'T' 
                   LEFT JOIN dbo.canceltable c ON o.OrderNo = c.OrderNo AND o.messageType = 'O' AND c.messageType = 'X' 
                   LEFT JOIN dbo.markingtable om ON o.OrderNo = om.OrderNo

WHERE     (o.EventTime BETWEEN '1/1/10' AND '5/1/10') 

AND (o.Symbol IN (........big list of symbols........))
GROUP BY o.Exchange, o.Symbol, o.Type, t.Quantity, o.Quantity, o.Price, om.OMarking, o.OrderNo, o.EventTime,t.EventTime,om.FPosition,c.EventTime
ORDER BY o.EventTime

Open in new window

Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
Last Comment
dbaSQL

8/22/2022 - Mon