i'm using Postgresql, my question is specifically about querying from a table that's in another table and i'm really having trouble with this one. In fact, i'm absolutely mentally blocked. I'll try to define the relations of the tables as much as I can.
I have a table entry which is like this:
Each of the entries has a group_id; when they are 'advanced' to the next stage, the old entries are marked is_active = false, and a new assignment is done, so C & D are advanced stages of A & B.
I have another table (which acts as a record keeper) , in which the storage_log_id refers to, this is the storage_log table :
But then I have another table, to really find out where the entries are actually stored - storage table :
To define my problem properly. Each entry has a storage_log_id (but some doesn't have yet), and a storage_log has a storage_id to refer to the actual table and find the storage label.
The sql query i'm trying to do should output this one:
Where The actual storage label is shown instead of the log id.
This is so far what i've done:
select e.id, e.group_id, e.name, e.stage, s.label
from operational.entry e, operational.storage_log sl, operational.storage s
where e.storage_log_id = sl.id and sl.storage_id = s.id
But this just returns 3 rows, showing only the ones that have the seed_storage_log_id set; I should be able to see even those without logs, and especially the active ones.
adding e.is_active = true to the condition makes the results empty. So, yeah i'm stuck.
Need help, Thanks guys!