How to get related record in same table (SQLite)
lazarus / 16-Databases

----------------------------------------------------------------------
mrguzgog · 2016-05-21 06:55

I'm trying to figure out the best way to access related records in the
same table in an SQLite database. Each row has an individual's details
and shows who that person's supervisor is via an ID number, something
like this:

IDSurnameInitialSupervisorUnits987JohnsonK 22,0001238SamuelsP987
18,500277DenzlerL987 19,540
So, in this case, Johnson supervises the other two individuals. I'd
like to have the list (dbgrid) on the left of the window (this could
be the entire record set or the results of an existing query) and on
clicking a record have the details displayed for the individual and
their supervisor in dbedit fields to the right of the list. I've got
it all set up apart from the last bit - do I just need another query
on the same table or is there a better/quicker/easier way to achieve
this?

For info, there's about a thousand records in the table (and more data
in each row than shown above).

Many thanks

----------------------------------------------------------------------
howardpc · 2016-05-21 07:13

From a design point of view an obvious solution would be to normalize
your data, and extract the appropriate individuals into a Supervisors
table.

----------------------------------------------------------------------
mrguzgog · 2016-05-21 11:37

That did occur to me except that some individuals can be supervisor
and supervised and there are other subtleties which would make it more
logical to keep them together to avoid duplication etc.

----------------------------------------------------------------------
paweld · 2016-05-21 11:50

Use join:Code: MySQL  [Select][+][-]window.onload = function(){var x1
= document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i select u.*,
s.surname supervisor_surname from table_user u left join table_user s
on (u.supervisor=s.id)

----------------------------------------------------------------------
mrguzgog · 2016-05-21 15:10

Thank you paweld. I'm fairly new to SQL and I don't understand your
code immediately - I'll have to read up some more, but it looks
promising!