troubleshooting Question

real quick problem with a join (urgent)

Avatar of dbaSQL
dbaSQLFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
49 Comments1 Solution435 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
ralmada

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 49 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 49 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros