How to create a datagrid for two no relationship tables

Hi, I am trying to create a create for two table A and table B which have no relationship each time. For TableA, there are 3 columns like ID, APoints1, APoint2. For Table B, there are also 3 columns as ID, Qty, BPoints. There is no internal relationship for these two tables. But there may be same ID inside A and B for some records. Now I want to create a datagrid for displaying the information as :

ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) WHERE A.ID = B.ID

Please Notice that I can't use directly SQL script as following from table A and table B because there is no relationship for Table A and Table B, otherwise the recult set would be wrong:

Select A.ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) WHERE A.ID = B.ID group by A.ID

May I know is there solution for it?

Thank you very much!There is a relationship:WHERE A.ID = B.ID.

Does the query below run and return what you are expecting?

Select A.ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints)as SomeName
From A inner join B ON (A.ID = B.ID)
Group by A.ID

|||No, the result set will be wrong because there is no relationship for A and B. Only some records for ID are the same. If I use the SQL query as you mention :

Select A.ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) as SomeName

From A inner join B ON (A.ID = B.ID)

Group by A.ID

Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) will be always the minus because A will returns correct records, but B records are more than the correct ones if there is only one B.ID= A.ID. For examples, when ID = 2, there are 3 records for A as following and there is 1 record only for Table B when B.ID= 2. But you will see the query returns wrong result for B, there are 2 more. So the result for 'Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints)' equal to 200, but in fact it should be 900.
A Table | B Table
ID A.APoints1 A.APoints2| Qty BPoints
2 100 200 1 300
2 100 300 1 300
2 100 400 1 300

That is why I can't directly use SQL query for it. Is there any way to use Dataset for it?|||But there is a relationship between A and B -- the ID.

You should be able to use a subquery and a LEFT OUTER JOIN to solve your problem within your query itself:

SELECT
 C.ID,
 C.SumAPoints - (ISNULL(B.Qty,0) * ISNULL(B.BPoints,0)) AS SomeName
FROM
(
SELECT
 A.ID,
 SUM(A.APoints1 + A.APoints2) AS SumAPoints
FROM
 A
GROUP BY
 A.ID
) AS C
LEFT OUTER JOIN B ON C.ID = B.ID

Terri|||Hi, if I use the SQL query as above, only C.SumAPoints is correct, but there are more than one records for each ID if the there are more than one records in Table B for the same ID. I need only one record for each ID as following :

ID Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints)
1 10
2 20
3 40

but from you query, I get the result as :

ID SomeName
1 40
1 30
1 20 <-- Only this record is what I need to display

Do you think that I need to remove myself for all those records I don't need but inside the query from your script?