Comparing result set values of 2 queries ?

Any assistance would be so helpful !!

We have 2 tables.. lets call them INV and COST

Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...

ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425

ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 ... different to ID 1 in INV Table
2 |10.524 |1.425

If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)

Thats it ?

Please could someone out there offer some ideas ?

THANKS

JONselect t1.[id] from [inv table] t1 inner join [cost table] t2 on t1.[id]=t2.[id] where t1.amount+t1.vat<>t2.amount+t2.vat|||Hi Rafala, thanks so much for the assistance,GREATLY APPRECIATED

I have a slight problem though...

Table COST can be made up of multiple entries/rows..ie, ID is not the primary key, so it is possible to have multiple rows, all with the same ID.

Table INV has single row entries for each ID(Primary Key)

Basically, INV table has 1 row, say ID 7
COST Table could have 4 rows, all ID 7. I need to add the AMOUNT and VAT columns for all 4 rows of Table COST and measure that up against the total (AMOUNT+VAT)for the single row of Table INV.

ie.

COST
id7 12 4
id7 21 7
id7 35 1
id7 10 87 ...TOTAL 177

INV
id7 78 99 ...TOTAL 177 ..In this case all is well

Should the TOTAL of ALL rows under cost.amount and cost.vat for cost.ID7 not equal TOTAL of inv.amount+inv.vat for inv.ID7, I would need it to bring up this problem ID...

Am I making much sense...

CHEERS|||Hi Rafala, thanks so much for the assistance,GREATLY APPRECIATED

I have a slight problem though...

Table COST can be made up of multiple entries/rows..ie, ID is not the primary key, so it is possible to have multiple rows, all with the same ID.

Table INV has single row entries for each ID(Primary Key)

Basically, INV table has 1 row, say ID 7
COST Table could have 4 rows, all ID 7. I need to add the AMOUNT and VAT columns for all 4 rows of Table COST and measure that up against the total (AMOUNT+VAT)for the single row of Table INV.

ie.

COST
id7 12 4
id7 21 7
id7 35 1
id7 10 87 ...TOTAL 177

INV
id7 78 99 ...TOTAL 177 ..In this case all is well

Should the TOTAL of ALL rows under cost.amount and cost.vat for cost.ID7 not equal TOTAL of inv.amount+inv.vat for inv.ID7, I would need it to bring up this problem ID...

Am I making much sense...

CHEERS|||select t1.[id] from [inv table] t1 inner join (select t2.[id], cost_total=sum(t2.amount+t2.vat) from [cost table] t2 where t1.[id]=t2.[id] group by t2.[id]) co where t1.[id] = co.[id] and (t1.amount+t1.vat)<>co.cost_total