real quick problem with a join (urgent)

The requirement is simple;  I need to return in ONE line, the details for each order message.  The ordertable is joined to itself to return O and T details, then it is joined to canceltable and marking table.
If canceltime is NULL, it just means it wasn't canceled.  IF ExecutionTime is NULL, it means it wasn't traded, or has no messageType = T, etc.

EXAMPLE:

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]

The 111 orderno is in ordertable three times, three message types: O, A, T.
It is not in the cancel table (wasn't canceled).
It is in the marking table once.  (always one record in marking table)

So, joining ordertable to itself on OrderNo, only worried about the O and the T, I want this:
NSDQ, GDX, 400, 400, 49.05, L, 111, 2010-01-10 09:00:11.4170000, 2010-01-10 09:00:11.4970000, 1320, NULL

just one thing. try your WHERE clause like below:

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
  ( select * from dbo.ordertable
WHERE     (o.EventTime BETWEEN '1/1/10' AND '5/1/10') 
AND (o.Symbol IN (........big list of symbols........))
) 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

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

Select all Open in new window

drop the alias there though:

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
  ( select * from dbo.ordertable
WHERE     (EventTime BETWEEN '1/1/10' AND '5/1/10') 
AND (Symbol IN (........big list of symbols........))
) 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

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

Select all Open in new window

you should be ordering by

ORDER BY o.OrderID, o.EventTime

or do this

FROM (select * from ordertable where o.messageType = 'O') o left join (select * from ordertable where o.messageType = 'T') t on o.orrderid=t.orderid
....

thank you, thank you, thank you, ralmada.

it still returns me three records, though.  I need a single record, per orderno.   The top record is correct, it has all the values from the table joined on itself, and the others.  I don't want the two with the NULL values.

do you know what i mean?

NSDQ	GDX	400	400	49.05000000	L	835YO3K3C42M    	2010-05-10 09:00:11.4170000	2010-05-10 09:00:11.4970000	1320	NULL
NSDQ	GDX	NULL	400	49.05000000	L	835YO3K3C42M    	2010-05-10 09:00:11.4200000	NULL	1320	NULL
NSDQ	GDX	NULL	400	49.05000000	L	835YO3K3C42M    	2010-05-10 09:00:11.4970000	NULL	1320	NULL

Select all Open in new window

ralmada, i just added this line into the inner select, to test one of the supsect orderno's:

AND OrderNo =       '835YO3K3C42M'

that's how i came up w/the result above

like this?

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
  ( select * from dbo.ordertable
WHERE     (EventTime BETWEEN '1/1/10' AND '5/1/10') 
AND (Symbol IN (........big list of symbols........))
) 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 t.OrderNo is not null 
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

Select all Open in new window

hainkurt, i can't do that, as the join must reference all three tables.  often times, there is no data in the canceltable, but more often, there is.  ralmada, that top line in the result i ran on one orderno is perfect.  i just want to dump the other two records that are returned.

So yeah, please my second suggestion a shot http:#a33599306

can you please try this

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
  (select * from dbo.ordertable where EventTime BETWEEN '1/1/10' AND '5/1/10' and Symbol IN (........big list of symbols........) and messageType = 'O') o
  LEFT JOIN (select * from dbo.ordertable where messageType = 'T') t ON o.OrderNo = t.OrderNo 
  LEFT JOIN (select * from dbo.canceltable c where messageType = 'X') c ON o.OrderNo = c.OrderNo
  LEFT JOIN dbo.markingtable om ON o.OrderNo = om.OrderNo
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

Select all Open in new window

almost, ralmada.  it works perfectly for the one orderno i referenced above:  '835YO3K3C42M'
only one record is returned, with all of correct data.

that orderno has an O, A and T in the ordertable, no records in the canceltable, and one record in the marking table.

if i do it on a message that has an O and A in the ordertable, and X and C in the canceltable, and one record in the markingtable, it gives back nothing.  i believe that is of course because there is no T in the ordertable.  but, i still need to return one line -- it will simply have NULL executiontime and NULL FQuantity, because it was not actuall traded.

That's why i was doing the LEFT JOIN on the ordertable.  Everything has an O, I need to derive the t.Quantity, t.EventTime, c.CancelTime and the om values, IF that OrderNo is also in ordertable as a T message, and IF that orderno is in the canceltable with an X, and IF that orderno is in the marking table.

very similar, hainkurt, but it also returns two records, when i expect one.
i'm trying it on an orderno right now that has two records in ordertable:  O, A
two records in canceletable:  X, C
one record in marking table.

i would expect one value returned with NULL executiontime and NULL fquantity, because there is no T message.

I am getting two records back, however.  and the ordertime for the second one is coming from the A messagetype.
I only need O and T from ordertable, and X from canceltable.

NSDQ      SSO      NULL      2900      41.17000000      L      FWBH3BDP1RSN          2010-05-10 07:11:53.7870000      NULL      158111      2010-05-10 07:12:01.4810000
NSDQ      SSO      NULL      2900      41.17000000      L      FWBH3BDP1RSN          2010-05-10 07:11:53.8030000      NULL      158111      2010-05-10 07:12:01.4810000

do either of you have any ideas?  i apologize, but this is becoming really very pressing.

I think you need something like
 

select * from (
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], row_number() over (partition by o.OrderNo order by o.OrderNo) rn
FROM
  (select * from dbo.ordertable where EventTime BETWEEN '1/1/10' AND '5/1/10' and Symbol IN (........big list of symbols........) and messageType = 'O') o
  LEFT JOIN (select * from dbo.ordertable where messageType = 'T') t ON o.OrderNo = t.OrderNo 
  LEFT JOIN (select * from dbo.canceltable c where messageType = 'X') c ON o.OrderNo = c.OrderNo
  LEFT JOIN dbo.markingtable om ON o.OrderNo = om.OrderNo
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
) a
where rn = 1
ORDER BY EventTime

Select all Open in new window

bless you. trying it now,  back shortly

if you have only one rec with 'O' in order table no need to group

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
  (select * from dbo.ordertable where EventTime BETWEEN '1/1/10' AND '5/1/10' and Symbol IN (........big list of symbols........) and messageType = 'O') o
  LEFT JOIN (select * from dbo.ordertable where messageType = 'T') t ON o.OrderNo = t.OrderNo 
  LEFT JOIN (select * from dbo.canceltable c where messageType = 'X') c ON o.OrderNo = c.OrderNo
  LEFT JOIN dbo.markingtable om ON o.OrderNo = om.OrderNo
ORDER BY o.OrderID

and this query should return one record as long as ordertable has one 'O' max for a given orderid

Select all Open in new window

try

SELECT 
o.Exchange,o.Symbol,t.Quantity [FQuantity],o.Quantity [OQuantity],o.Price,om.OMarking,
o.OrderNo,o.EventTime [OrderTime],t.maxevent [ExecutionTime],om.FPosition,c.EventTime [CancelTime]
FROM (select * from dbo.ordertable 
       WHERE messagetype='O'
         and EventTime BETWEEN '1/1/10' AND '5/1/10' 
         AND Symbol IN (........big list of symbols........))
      ) as o 
LEFT outer JOIN 
    (select orderno,sum(quantity) as quantity,max(eventtime) as maxevent
       from dbo.ordertable 
     where messagetype='T' 
      group by orderno
    ) as t ON o.OrderNo = t.OrderNo 
LEFT outer JOIN 
    (select * from dbo.canceltable 
      where messagetype='X'
    ) as c ON o.OrderNo = c.OrderNo 
LEFT outer JOIN dbo.markingtable om 
  ON o.OrderNo = om.OrderNo
ORDER BY o.EventTime

Select all Open in new window

well, i thought we were there, ralmada.  until i saw that order messages that had multiple T's, were not accounted for in the output.  for example,
-I've got one in ordertable 13 times.  (one O, one A, eleven T's)
-it is not in cancel table (wasn't canceled)
-it is in the marking table.  

Your new suggested construct returns this:
BEX        AMD      100         1900       8.88000000          S              FWBL6J2Z23OX                 2010-05-10 10:08:36.1230000       2010-05-10 10:08:36.1230000              -6205     NULL

But, the fQuantity is the quantity from only one of the T messages.

I have asked up top, and I am told that we need to treat each T separately because the executiontime impacts the firm's position at that time.  I was in error by attempting to return the T messages collectively, as opposed to each in their own returned values, per orderno.

So I need to return one line for the order – which is the composite of the O message and the X message from the JOIN between ordertable and candeltable, on orderno.   this record woud have NULL executiontime and NULL fquantity, and it should be sequenced by each of the T messages, in lines of their own, each explicitly returned.  maybe there will be only one, maybe there will be more than one, but each T message needs to come back on its own.  IF there are more than one, they all have the same OrderTime, but each has its own ExecutionTime and FQuantity.

I'm sorry, ralmada.  i more than appreciate your assistance with this.  can it be done?

ralmada, lowfat and hainkurt, thank you very much for all of your time.  i apologize for the mistaken post.  each of your suggestions is very close to, or perfect, for the requested  single line result.  i'm trying to get the T messages out after that line, though.  

first line is just the composite between ordertable and canceltable
second or multiple lines thereafter are the T messages, if exists.  

ExecutionTime and FQuantity are NULL in the first record, and included in each of the subsequent T messages, if exists.  

what do you think?

Lowfatspread, is your query different than the one I posted @ 33599719 (I could not locate the difference)

you should really post some sample data

OrderTable
1 O 1/1/2010
1 T 2/1/2010
1 T 3/1/2010
1 T 4/1/2010

CancelTable
1 X

MarketingTable
1 M
 
and query (should) return

1 O 1/1/2010 T 2/1/2010 X M
1 O 1/1/2010 T 3/1/2010 X M
1 O 1/1/2010 T 4/1/2010 X M

because we have 1 O, 3 T, 1 X, 1 M (1 * 3 * 1 * 1 = 3 records...)

so where is the problem here? and you keep saying you need one record! how you can get one record if you have multiple T's? do you want first T, last T or all T's (which you have now)

Select all Open in new window

I got the difference

LEFT JOIN vs. LEFT OUTER JOIN

but they are same, OUTER is optional value...

hainkurt, i clearly indicated in post 33600044 and 33600157, that i was in error by attemtpting a single line resultset.  the T messages have to be returned separately.

the example you posted should return four lines -
- first line is the composite between ordertable and canceltable, but ExecutionTime and FQuantity are NULL.  
- The subsequent three lines would be the T messages, ExecutionTime and FQuantity are populated from each of the T messages.

Again, I was in error.  I still need to perform the same LEFT construct -- all messages have an O, only some have T's, and cancels, and marking messages.  IF the orderno has multiple T messages, i need to return them each, distinctly

It will be better if you can post some sample data and the expected result (what is the end result from the query) along with the rationale as to why it has to be like this

hopefully this will post well

ordertable
messagetype,endpoint,symbol,quantity,price,orderno,eventtime
O	BEX	AMD	1900	8.88000000	FWBL6J2Z23OX    	2010-05-10 10:08:36.1230000
A	BEX	AMD	1900	8.88000000	FWBL6J2Z23OX    	2010-05-10 10:08:36.1230000
T	BEX	AMD	100	8.88000000	FWBL6J2Z23OX    	2010-05-10 10:08:36.1230000
T	BEX	AMD	200	8.88000000	FWBL6J2Z23OX    	2010-05-10 10:08:36.1230000
T	BEX	AMD	200	8.88000000	FWBL6J2Z23OX    	2010-05-10 10:08:36.1230000
T	BEX	AMD	200	8.88000000	FWBL6J2Z23OX    	2010-05-10 10:08:36.1230000
T	BEX	AMD	100	8.88000000	FWBL6J2Z23OX    	2010-05-10 10:08:36.1230000
T	BEX	AMD	100	8.88000000	FWBL6J2Z23OX    	2010-05-10 10:08:36.1230000
T	BEX	AMD	100	8.88000000	FWBL6J2Z23OX    	2010-05-10 10:08:36.1230000
T	BEX	AMD	300	8.88000000	FWBL6J2Z23OX    	2010-05-10 10:08:36.1230000
T	BEX	AMD	100	8.88000000	FWBL6J2Z23OX    	2010-05-10 10:08:36.1230000
T	BEX	AMD	100	8.88000000	FWBL6J2Z23OX    	2010-05-10 10:08:36.1230000
T	BEX	AMD	400	8.88000000	FWBL6J2Z23OX    	2010-05-10 10:08:36.1230000

canceltable
eventtime, orderno, messagetype
this order was not canceled, it is not in the canceltable

markingtable
eventtime, orderno, omarking, fposition
2010-05-10 10:08:36.1230000,FWBL6J2Z23OX, S, -6205


result:
Exchange 	Symbol	FQuantity 	OrderQuantity Price 		OMarking     OrderNo 	            OrderTime 			ExecutionTime 		FPosition CancelTime
BEX	AMD	NULL	    1900	8.88000000		S	  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	NULL	       		-6205	  NULL
BEX	AMD	100	    1900	8.88000000		S	  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	  NULL
BEX	AMD	200	    1900 	8.88000000		S	  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	  NULL
BEX	AMD	200	    1900	8.88000000		S	  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	  NULL
BEX	AMD	200	    1900	8.88000000		S	  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	  NULL
BEX	AMD	100	    1900	8.88000000		S	  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	  NULL
BEX	AMD	100	    1900	8.88000000		S	  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	  NULL
BEX	AMD	100	    1900	8.88000000		S	  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	  NULL
BEX	AMD	300	    1900	8.88000000		S	  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	  NULL
BEX	AMD	100	    1900 	8.88000000		S	  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	  NULL
BEX	AMD	100	    1900	8.88000000		S	  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	  NULL
BEX	AMD	400	    1900	8.88000000		S	  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	  NULL

Select all Open in new window

didn't post as well as i'd hoped.  but the first chunk just lists the messages in the ordertable, you will see the O, A and elevent T's.
Second chunk is NULL because this order was not canceled.  IF it had been, there would be an X in there.
third chunk is the marking table.

last chunk is the intended result.  you see there are twelve lines.  one for the O message and X, if exists, along with markingtable details.  and then eleven for each of the T messages.  This one is kind of extreme.  typically you get 1 to 4 T messages, per OrderNo.  sometimes, however, there are no T messages.

please let me know if it is unclear, ralmada

also, you see the OrderQuantity = 1900.  That is the quantity the original order was submitted as.  If you sum the quantity of each of the T's, you get 1900.

and remember, we don't care about the A messages (don't want them)

What about like this then?

select 
	o.endpoint,
	o.symbol,
	case when o.messagetype = 'T' then o.quantity end as FQuantity,
	(select sum(quantity) from ordertable where orderno = o.orderno and messagetime = 'O') as OrderQuantity,
	o.Price,
	m.omarking,
	o.OrderNo,
	o.OrderTime,
	o.eventtime as ExecutionTime,
	m.fposition,
	c.eventime		
from (
	select * from ordertable o
	where o.messagetype in ('O','T')
) o
left join markingtable m on o.orderno = m.orderno
left join canceltable c on o.orderno = c.orderno
order by o.orderno, o.messagetype desc

Select all Open in new window

Almost perfect, ralmada.  All of the correct records are displayed, only the ordermessage that is supposed to be first, is last.  and the executiontime is not NULL.  it should be NULL for any record that is not a T message.

hopefully it will post well

i'm going to try it out with some other ordernos, that have different conditions.  (those with cancels, those without T's)

i thought i'd order by quantity, to get the NULL fquantity up top, but it didn't work out.

BEX  AMD  100    1900	8.88000000	S  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	NULL
BEX  AMD  200    1900 	8.88000000	S  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	NULL
BEX  AMD  200    1900	8.88000000	S  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	NULL
BEX  AMD  200    1900	8.88000000	S  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	NULL
BEX  AMD  100    1900	8.88000000	S  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	NULL
BEX  AMD  100    1900	8.88000000	S  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	NULL
BEX  AMD  100    1900	8.88000000	S  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	NULL
BEX  AMD  300    1900	8.88000000	S  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	NULL
BEX  AMD  100    1900 	8.88000000	S  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	NULL
BEX  AMD  100    1900	8.88000000	S  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	NULL
BEX  AMD  400    1900	8.88000000      S  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	NULL 
BEX  AMD  NULL   1900	8.88000000	S  FWBL6J2Z23OX  2010-05-10 10:08:36.1230000	2010-05-10 10:08:36.1240000	-6205	NULL

Select all Open in new window

i haven't tested the other message types yet, and i have to run a quick errand.  i'll be back soon, and I wiil sort as you've suggested (and correct my executiontime).  back soon, ralmada.  thank you for all of the assistance

Though the o message is still ordering at the bottom, I think we are much closer.
I have checked the following types of messages:

835YO3K3C42M    This orderno has an O, A and one T in ordertable, no cancels, and one marking.
Returns two records, the first is the composite between ordertable.O, cancels (none exist), and marking, where executiontime and fquantity are NULL.
The second is the T message, and executiontime and fquantity are not null.

835YO3K3C42O   This orderno has an O and A in ordertable, an X in canceltable, no marking record.
Returns one record, where executiontime and fquantity are NULL (no T's), and canceltime is the eventime from canceltable.

FWBH2POLKCXT  This orderno has an O, an A and four T messages in ordertable, no cancels and one marking.
Returns as five records, where execuitontime and fquantity are NULL in the first, and populated in the remaining four (the T messages).

FWBL6J2Z23OX  This orderno has an O, an A and eleven T messages in ordertable, no cancels and one marking.
Returns twelve records, where executiontime and fquantity are NULL in the first, and populated for the remaining eleven (the T messages).

alll very good.

FWBH3BDP1RSN  This orderno has an O and A in ordertable, (no T's), an X and C in canceltable, and one marking.
Returns two records -- it should onlly return one.  

The return is pasted below, it is returning a record for both the X and the C in the canceltable.  

In the ordertable we only care about O and T.  We should ignore the A's.
In the canceltable we only care about the X.  We should ignore the C's.

I changed my canceltable LEFT JOIN to this:
LEFT JOIN dbo.canceltable c ON o.OrderNo = c.OrderNo AND c.MsgType <> 'C'

It works, it only returns one record, for the ordertable.O, canceltable.X, and markingtable.

ralmada, what do you think?  there are millions and millions of order messages.  of those that I checked above, I think that's a pretty decent variety.  they're all good (with my changed LEFT JOIN).

what do you think?

NSDQ SSO	NULL 2900  41.1700  L  FWBH3BDP1RSN  2010-05-10 07:11:53.7870000  NULL  158111  2010-05-10 07:12:01.4810000
NSDQ SSO	NULL 2900	  41.1700  L  FWBH3BDP1RSN  2010-05-10 07:11:53.7870000  NULL  158111  2010-05-10 07:12:01.4970000

Select all Open in new window

Still can't get the o message up top the list, though.

ORDER BY o.OrderNo, o.messagetype DESC

sorry long night I guess, it should be ASCENDING (since O comes first!)
ORDER BY o.OrderNo, o.messagetype  
Regarding your previous question, sounds fair enough filtering by msgtype <> 'C'

no apologies necessary, ralmada.  i think it is good.  i have put a great deal of data together, and am analyzing it.  again, i think it is good.  but i can't seal the deal until my associates review come tuesday am.  I will let you know.  again, thank you for all of your assistance with this.  very much appreciated.
i'll be back soon

just keeps getting better and better.  :-(

the canceltable has X and C's -- the X is preferred in the left join from ordertable to canceltable.  BUT, if there is only a C, I need to join to it, instead.

If there is an X and a C, give me the X
If there is only an X, give me the X
If there is only a C, give me the C

Can I CASE that LEFT JOIN, on the messagetype, ralmada?

left join canceltable c on o.orderno = c.orderno

LEFT JOIN dbo.canceltable c ON o.OrderNo = c.OrderNo
      AND (CASE WHEN c.messagetype = 'X' THEN 1
ELSE (CASE WHEN c.messagetype = 'C' THEN 1 ELSE 0 END) END)=1


still not quite right.  I need another condition in there -

when c.messagetype in ('x','c') then X

still working it

LEFT JOIN dbo.canceltable c ON o.OrderNo = c.OrderNo
      AND c.messagetype = COALESCE('X','C')

yeah?

well, it seems appropriate for the conditional join,  but my record counts dropped by more than half.  still looking.

>>If there is an X and a C, give me the X
If there is only an X, give me the X
If there is only a C, give me the C<<

what about this?

LEFT JOIN (
		select t1.* from dbo.canceltable t1
		where t1.MsgType = (select max(MsgType) from dbo.cancelTable where OrderNo = t1.OrderNo)

	) c ON o.OrderNo = c.OrderNo

Select all Open in new window

If there is an X and a C, how does that guarantee I will use the X, ralmada?

because the max(MsgType) will always give X if there's an X and C. IF there's a C only it will bring C, and finally if there's only an X, it will bring an X.

ok ralmada.  99.9% there.  seriously sweet.  thank you.

      o.OrderTime,
      case when o.messagetype = 'T' then o.eventtime end as ExecutionTime,

I need the o.OrderTime to always be the EventTime of the O message.

I cased it, but the OrderTime is coming back NULL for the T messages.  It should be the same, the Eventtime for the O message, for all related T messages.

Make sense?

i got it:

  OrderTime = (SELECT EventTime FROM dbo.ordertable WHERE OrderNo = o.OrderNo AND messagtype = 'O'),

back in a bit with status

change the
  case when o.messagetype = 'T' then o.eventtime end as ExecutionTime
to
 (select max(eventtime) from ordertable where orderno = o.orderno and messagetime = 'O') as Executiontime,

ok, I guess you figured it out. I would still use the select max(eventtime) rather than the plain select eventtime
 

will do.  thank you, ralmada.  

I think we got it, ralmada.  There is an off chance that something else will seem amiss in the output.... I researching orderno's and answering questions non-stop since I ran the last version of this construct.  Right now, however, it looks good.

I want to thank you each for all of the input.  extra thanks to you, ralmada.  very pointed and direct logic, easy to follow, and extremely helpful

i say that in retrospect, of course.... :-)