Help

Join table to aggregate

Topic Labels: Base design
Solved
Jump to Solution
1353 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Timothy_Keitt
5 - Automation Enthusiast
5 - Automation Enthusiast

I am trying to figure out how to have two columns in some sort of table or report where one is drawn from one table and another is matched according to an aggregate on another table. In SQL, this would roughly look like

SELECT a.name, a.target, sum(b.value) FROM TAB1 a, TAB2 b WHERE a.name = b.name GROUP BY a.name

Here, unique names occur once in a but many times in b so that many rows of b match to each row of a.

TAB1

Name. Target
A 1
B 2
C 3

TAB2

Name Value
A. 5
A 3
B 2
C 3
B 6
C 4

1 Solution

Accepted Solutions
Timothy_Keitt
5 - Automation Enthusiast
5 - Automation Enthusiast

I see now that something like this is possible by linking from TAB1 to TAB2 and then adding a rollup field.

This works but it requires manually linking the two tables. This seems a mind-boggling omission in that there is no way I can see to link the tables based on a condition. Is it true that there is no SQL WHERE equivalent? It seems such an obvious thing to extend linking with a conditional expression matching two fields between tables.

See Solution in Thread

1 Reply 1
Timothy_Keitt
5 - Automation Enthusiast
5 - Automation Enthusiast

I see now that something like this is possible by linking from TAB1 to TAB2 and then adding a rollup field.

This works but it requires manually linking the two tables. This seems a mind-boggling omission in that there is no way I can see to link the tables based on a condition. Is it true that there is no SQL WHERE equivalent? It seems such an obvious thing to extend linking with a conditional expression matching two fields between tables.