I have 3 tables (I'll call them A, B and C for now). Each has a primary key ID and table B has an additional PK called POS

A is my primary table and has an amount column (SubTotal)
B is a many to 1 table relating to A (i.e. A.ID = B.ID)

Within table B is a column (we'll call it CSEQ here). The key to table C is A.ID+B.CSEQ (i.e. concatenated)

Table C has an amount column I wish to SUM (TranAmount)

There are more A rows than there are matching C rows (i.e. not every A.ID appears in table C)

In pseudo SQL terms I want something like this:

SELECT A.ID FROM A, C WHERE (A.SubTotal - SUM(C.TranAmount)) > 100

I realise I need some sort of JOIN on A.ID = B.ID and C.ID = (A.ID + B.CSEQ) but my SQL knowledge is pretty basic. Every time I think I have it I'm missing rows or my C.TranAmount column is always null.

in pseudo SQL terms you can do something like this:

SELECT ...
FROM A
LEFT OUTER JOIN B ON B.ID = A.ID
LEFT OUTER JOIN C ON C.ID = A.ID + B.CSEQ

alternatively, that last line might have to be

LEFT OUTER JOIN C ON C.ID = A.ID || B.CSEQ


further help i cannot give, since your specs are so vague

But if I do something as basic as that (which I already tried) I run into a multitude of problems:

select A.ID, SUM(A.Amount), SUM(isnull(0,C.TranAmount)) FROM
A
left outer join B
ON A.ID = B.ID
left outer join C
ON C.ID = (A.ID + B.CSEQ)
group by A.ID
order by A.ID

Firstly, the SUM of A.Amount is multiplied by the number of rows in table B that matches table A (which is obviously wrong as I stated originally, I just want A.Amount). If I don't use the SUM on A.Amount I get an aggregate/GroupBy error (which I "sort of" understand).

Additionally, using this basic syntax I can't work out how to do a where clause for the value range I'm after.

I thought I was getting close with one of my earlier attempts:

SELECT AID FROM
(SELECT ID as AID, Amount FROM A) A JOIN
(SELECT A.ID, B.CSEQ FROM A, B WHERE A.ID = B.ID) B ON A.ID = B.ID
LEFT OUTER JOIN
(SELECT ID, SUM(isnull(TranAmount,0)) AS Camt FROM C GROUP BY ID) C ON C.ID = (A.ID+B.CSEQ)
WHERE A.Amount - C.Camt > 100 and A.Amount - C.Camt < 200
ORDER BY A.Amount - C.Camt, A.ID

In this example I am at least not duplicating the A.Amount value but I know my ...FROM C group by ID doesn't make sense as I really want to be grouping on A.ID (at least that's my way of thinking) but I couldn't work out the syntax.

then perhaps it's time for you to share the actual details of your actual tables rather than this --

SELECT A.ID FROM A, C WHERE (A.SubTotal - SUM(C.TranAmount)) > 100

which merely requires

... GROUP BY A.ID, A.SubTotal

along with the necessary joins


so this is just another example of a thread where i got lost due to over-simplification

good luck!!

I've made it simple because other than changing the table names I don't believe I've left anything out.

Here's a script to create/populate/select/drop. The select just needs to be finished. I don't know what else I could be providing as info. I don't understand why grouping by SubTotal makes sense.

CREATE TABLE A (ID VARCHAR(10), SubTotal DECIMAL(5,2))
GO
CREATE TABLE B (ID VARCHAR(10), POS INT, CSEQS VARCHAR(2))
GO
CREATE TABLE C (ID VARCHAR(12), TranAmount DECIMAL(5,2))
GO
INSERT INTO A VALUES('12345', '100')
GO
INSERT INTO A VALUES('12346', '100')
GO
INSERT INTO A VALUES('12347', '101')
GO
INSERT INTO B VALUES('12345', 1, '01')
GO
INSERT INTO B VALUES('12345', 2, '02')
GO
INSERT INTO C VALUES('1234501', 50)
GO
SELECT A.ID FROM A, C WHERE (A.SubTotal - SUM(C.TranAmount)) > 100
GROUP BY A.ID, A.SubTotal
GO
DROP TABLE A
GO
DROP TABLE B
GO
DROP TABLE C
GO

if you want it in the SELECT clause of the query, you ~must~ do it, based on ANSI SQL syntax rules (the forum you posted in)

hey, what happened to B?

oh, wait... my bad

A.Subtotal wasn't in the SELECT clause, it was in the WHERE clause

well, that's not going to work, it has to be in the HAVING clause because of the GROUP BY

SELECT A.ID FROM A, C
GROUP BY A.ID, A.SubTotal
HAVING (A.SubTotal - SUM(C.TranAmount)) > 100

I see where you're coming from but your example over simplifies my problem.

What happened to B? Well that's (one of) the parts I can't work out. You mentioned "along with the necessary joins". If I knew what they were I (possibly) wouldn't be posting the question in the first place (I realise you're only trying to help...and I appreciate it). Quoting myself "my SQL knowledge is pretty basic".

Given that:

A.ID + B.CSEQ = C.ID

There are As which don't have the above matches (i.e. OUTER JOIN resulting in SUM(SubTotal) = 0).

Expanding on the above statement, there are A.IDs which don't have matching B.IDs. There are also A.ID + B.CSEQ combinations which don't have matching C.IDs.

The small example of INSERTs I posted shows this.

okay, time to stop pussyfooting... what database system is this? or would you like the ANSI SQL version?

also, for the sample data you provide, what should the query return?

To stop "pussy footing" as you say...I'm working on a program to translate a query from another environment (non-RDBMS) to SQL.

In this example it's MSSQL...but ideally I'm looking for ANSI cos it won't always be MSSQL. But I am prepare to use DB specifics if required (as long as there's a solution for MSSQL, DB2, Oracle...maybe MYSQL).

I realise I over simplified my example. Replace the inserts with this:

INSERT INTO A VALUES('12345', '150');
INSERT INTO A VALUES('12346', '200');
INSERT INTO A VALUES('12347', '101');
INSERT INTO B VALUES('12345', 1, '01');
INSERT INTO B VALUES('12345', 1, '02');
INSERT INTO B VALUES('1234', 1, '01');
INSERT INTO B VALUES('1234', 1, '02');
INSERT INTO C VALUES('1234501', 50);
INSERT INTO C VALUES('123401', 50);
INSERT INTO C VALUES('123402', 50);

and change the where clause to be > 100 and < 200.

I'd expect to see 12347.

the database systems you mentioned vary wildly in their implementation of standard SQL concatenation

let's use the plus sign for now, which MSSQL likes but the others don't (see my remark in post #2 )
is this the correct data?

That data looks correct. So all I need now is grouping on A.ID and producing a SubTotal - SUM(TranAmount) value in a > 100 < 200 expression.

got a couple extra, dintcha

I do believe I said > 100 and < 200 and also it's - not + (which excludes the 100 values)

However, having said that...you're a champ. I've run this against my full table setup and the results match exactly what I expect.

Thanks for sticking this one through.

Now to see if I can produce that programmatically....sigh.

I've had a new spanner thrown into the works. I've need to produce a similar report to:

SELECT A.ID
FROM A
LEFT OUTER
JOIN B
ON B.ID = A.ID
LEFT OUTER
JOIN C
ON C.ID = A.ID + B.CSEQS
GROUP
BY A.ID
, A.SubTotal
HAVING A.SubTotal - COALESCE(SUM(C.TranAmount),0)
BETWEEN 100 AND 200

But this time instead of A.SubTotal I need SUM(D.Amount) where D.Amount is a joined table of A.ID = D.ID.

I tried

SELECT A.ID
FROM A
LEFT OUTER
JOIN B
ON B.ID = A.ID
LEFT OUTER
JOIN C
ON C.ID = A.ID + B.CSEQS
LEFT OUTER
JOIN D
ON D.ID = A.ID
GROUP
BY A.ID
HAVING COALESCE(SUM(D.Amount),0) - COALESCE(SUM(C.TranAmount),0)
BETWEEN 100 AND 200

but I'm not getting as many rows as I expect. I think I understand why (intersecting on tables C and D?...which I don't want) but - obviously - don't know the solution.