I have the following schema in my database
Neighbors: ( central_id, neighbor_id)
Data: (central_id, value)
This schema is for a convolution algorithm which for each central_id
I have 6 neighbors(it is hexagonal grid)
for example here is a part of my Neighbors table
Central_id|Neighbor_id
0|1
0|2
0|3
0|4
0|5
0|6
and here is my data
central_id|value
0|20
1|1
2|6
3|1
4|6
5|1
6|0
I want to join these tables in a way that I can sum value
for each central_id
my final result is sth like this
central_id|sum
0|(1+6+1+6+1+0=15)
Here is some of my tries
this query gives me the central_id|neighbor_id|value
select nb.c ,ac.c,ac.value
from
neighbors as nb
inner join data as ac on nb.neighbour_id=ac.central_id
but it's result is wrong, since value is 20
which is value of central_id.
this is my second try
select c as nb,value from data where c in(
select nb.c
from
neighbors as nb
inner join data as ac on nb.neighbour_id=ac.central_id
where and ac.c=0
)
this query result is correct. It returns neighbour_id
and it's value, but I can not sum them based on central_id
If I could end up with such result my problems would be solved
central_id|neighbour_id|value(of each neighbour_id not central_id)
0|1|1
0|2|6
0|3|1
0|4|6
0|5|1
0|6|0
Can some one give me some hints about fixing this problem?thanks
Best Answer
This should work: