ansi SQL help

Hello,

I have 3 tables.

orders
--
orderID PK
symbol
price
accountNumber
matchStatus
orderDate
volume
side

executions
--
executionID
symbol
price
mcName
orderDate
orderTime
volume
side

accounts
----
accountID
accountNumber
mcName

What I have so far.

select o.orderid, abs(o.volume), sum(e.volume)
from orders o, executions e, accounts a
where a.accountNumber = o.accountNumber and e.mcName = a.mcName
and o.matchStatus IS NULL and e.orderID IS NULL
and left(e.side, 1) = left(o.side, 1)
and o.orderDate = e.executionDate
and o.symbol = e.symbol
group by o.orderid, e.volume, o.volume
having sum(e.volume) = abs(o.volume)

************************************************** 8
I put this in the select statement as a debug statementm abs
(o.volume), sum(e.volume).

What I need to do is this.

I need to get the orderID from the orders table for records where all
the where statement clauses match up and the abs value of o.volume
equals the sum of v.volume for all records that match the the where
clause. It will always be one record in the orders table and will be 1 or
more records in the execution table that will match up.

How do I do this?

Right now I get 0 records because the group by isn't working to well.

here is a sample return set if I don't have the HAVING statement.

ORDERID ABS SUM
2219 434 271
2219 434 500
4125 2000 400
4125 2000 700
4125 2000 900
4129 2000 300
4129 2000 400Take e.volume out of the GROUP BY clause and you should get:

select o.orderid, abs(o.volume), sum(e.volume)
from orders o, executions e, accounts a
where a.accountNumber = o.accountNumber and e.mcName = a.mcName
and o.matchStatus IS NULL and e.orderID IS NULL
and left(e.side, 1) = left(o.side, 1)
and o.orderDate = e.executionDate
and o.symbol = e.symbol
group by o.orderid, abs(o.volume);

ORDERID ABS SUM
2219 434 771
4125 2000 2700

Then put your HAVING back in.|||Thanks worked like a charm.
Do you mind if I ask you another question?

How about if I want to to do an update statement on the returned recordset.

Something like

update executions
set orderID =
(
select o.orderid
from orders o, executions e, accounts a
where a.accountNumber = o.accountNumber and e.mcName = a.mcName
and o.matchStatus IS NULL and e.orderID IS NULL
and left(e.side, 1) = left(o.side, 1)
and o.orderDate = e.executionDate
and o.symbol = e.symbol
group by o.orderid, abs(o.volume)
having sum(e.volume) = abs(o.volume)
)
WHERE orderID IS NULL

Unfortunately my subselect returns multiple records.
What is the best way around this?|||I don't really understand what you want to do.|||I want to update executions.orderid to orders.orderid where the conditions are met.

I found my old code and it went like this.

UPDATE executions
SET orderID =
(
SELECT O.orderID
FROM orders AS O JOIN accounts AS A ON O.accountnumber = A.accountnumber
WHERE O.symbol = executions.symbol AND A.mcname = executions.mcname
AND LEFT(o.side, 1) = LEFT(executions.side, 1) AND O.orderDate = executions.executionDate
AND O.orderID in
(
select o2.orderID
from orders o2, executions ee2, accounts aa
where LEFT(o2.side, 1) = LEFT(ee2.side, 1) and o2.symbol = ee2.symbol and
aa.MCName = ee2.MCName AND aa.accountNumber = o2.accountNumber AND o2.orderDate = ee2.executionDate
AND ee2.orderID IS NULL and o2.matchStatus IS NULL
group by o2.orderID, o2.volume
HAVING SUM(ee2.volume) = ABS(o2.volume)
)
)
WHERE orderID IS NULL

update orders
set matchStatus = 1
where orders.matchStatus IS NULL AND orders.orderID IN
(
select executions.orderID
from executions
where executions.orderID IS NOT NULL
)