I'm trying to build a table from data stored on 3 separate tables that are used to determine how user accounts are related towards each other. The tables are very simple, I can explain the structure here.
characters
unique_id | name
1 Jim
2 Tim
relations
unique_id | name
1 Hated
2 Enemies
3 Friends
4 Neutral
connections
unique_id | sender | receiver | relation
1 | 1 | 1 | 1
2 | 1 | 2 | 3
3 | 2 | 1 | 2
4 | 2 | 2 | 4
sender/receiver are both integers which match a characters unique_id.
relation is a integer which match a relations unique_id.
So, how I want the table to work is simple enough. The table will generate a row and column for each character(users), then where the row and column intersects it will state what the relation is for the two characters.
x Jim Tim
Jim Hated Friends
Tim Enemies Neutral
It seems simple enough in theory but I can not figure out a method to do this without making a query for each cell in the table (looking up sender 1 receiver 1, then looking up sender 1 receiver 2) I know that would work, but that many queries seems inefficient. I feel like I'm missing something obvious, but I can't put my finger on it.