0

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

  • I'm confused as to how you propose to join the first table where every central_id is 0 to your second table where there is only one record with central_id = 0? – Vérace Jul 1 '19 at 7:30
  • @Vérace My main goal is to find neighbors based on central id – Majid Hojati Jul 1 '19 at 23:06
1

This should work:

SELECT a.central_id, sum(b.value) 
FROM neighbors AS a
JOIN data AS b
  ON a.neighbor_id = b.central_id
GROUP BY a.central_id;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.