Sum of value where one (unlinked) column is equal to another

[Mauritz_SA]

Hi everyone

Because of the way my data model is built, I have some rows that aren't linked as they are not in a transactions table. Long story short, I essentially have the three tables below:

List 1:

Id1	Col1	Points
1	A	10
2	B	20
3	C	10
1	A	20
2	B	10
3	C	25

List 2:

Id	Column	Class
1	A	X
2	B	Y
3	C	Y

Join Table:

Id	Id1
1	1
2	2

When I allow the natural concatenation to take place I am left with the following:

What I want to achieve is to be able to get the sum of the values, grouped by Class where my Column value is equal to my Col1 value (regardless of what is in my Join Table):

Class	Sum(Points)
X	30
Y	65

I know that the tables can be joined differently, but this is a simplification of a standard data model which is being used for many clients and we want to try and keep it standard if possible.

Any help would be appreciated.

Regards,

Mauritz

PS. Below the script to recreate the tables:

[List 1]:
LOAD * INLINE [
Id1,Col1,Points
1,A,10
2,B,20
3,C,10
1,A,20
2,B,10
3,C,25
];

[List 2]:
LOAD * INLINE [
Id,Column,Class
1,A,X
2,B,Y
3,C,Y
];

[Join Table]:
LOAD * INLINE [
Id,Id1
1,1
2,2
];