Join two tables using sum and max

I've got two tables, one called clientsharedeals and the clientorderdeals. In the first table, I have four fields (Rundate, Accno, Dealid, Nominal) that I need to sum(Nominal), grouping by dealid.

Once I've done this, I need to join to clientorderdeals, also having the same fields plus one extra (Rundate, Accno, Dealid, Nominal and Dealseq). Because of Dealseq, I can have more than one row in the table, matching (Rundate, Accno, Dealid, Nominal) of the first table. However, Dealseq increments, so I need to select max(Dealseq).

My query is doubling up on nominal because in my select statement, I am only using one account number, so I know what the value is for nominal and there are two rows in clientorderdeals - and it is not selecting max(dealseq) but both.

Can someone please cast some pearls my way ?

ThanksThis may not be what you want, but if you post your question in the following manner with the expected results, I'm sure you'd get an answer rather quickly

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Rundate datetime, Accno int, Dealid int, Nominal int)
CREATE TABLE myTable00(Rundate datetime, Accno int, Dealid int, Nominal int, Dealseq int)
GO

INSERT INTO myTable99(Rundate, Accno, Dealid, Nominal)
SELECT '1/1/2005',1,1,1 UNION ALL
SELECT '1/1/2005',1,2,1 UNION ALL
SELECT '1/1/2005',1,3,1 UNION ALL
SELECT '1/1/2005',1,4,1

INSERT INTO myTable00(Rundate, Accno, Dealid, Nominal, Dealseq)
SELECT '1/1/2005',1,1,1,1 UNION ALL
SELECT '1/1/2005',1,2,1,1 UNION ALL
SELECT '1/1/2005',1,3,1,1 UNION ALL
SELECT '1/1/2005',1,1,1,2 UNION ALL
SELECT '1/1/2005',1,2,1,2 UNION ALL
SELECT '1/1/2005',1,3,1,2 UNION ALL
SELECT '1/1/2005',1,4,1,1
GO

SELECT *
FROM (
SELECT Dealid, SUM(Nominal) AS SUM_Nominal
FROM myTable99
GROUP BY Dealid) AS xxx
JOIN ( SELECT *
FROM myTable00 a
WHERE DealSeq = (SELECT MAX(Dealseq)
FROM myTable00 b
WHERE a.Dealid = b.Dealid)) AS yyy
ON xxx.Dealid = yyy.Dealid

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO