Table using same database table for a selection

BoSmith

Hi Allen,
I do not have example code because I could not figure out if this is possible:

Say I have a database table called users. The table contains basic data about my users at our school. One field states the role of the user such as student, parent, teacher, etc. I would like to be able to have a select drop down so that I can assign a parent to a student. The trick is they both reside in the same table. So that table would have a parent_id that would be in the same record as the student. I am trying not to have separate tables for different roles.

Example
id, role, first_name, last_name, parent_id
1, student, tom, smith, 2
2, parent, jeff, smith, null

Is this possible? If so, could I get a point in the right direction?

Thanks again,
Bo

allan

Hi Bo,

have a look at this example. It shows how a self referencing join can be done to join data from a table to the same table.

From my understanding of what you are looking for, that would do what you need. Is that correct?

Allan

BoSmith

Perfect! That is exactly it. Much easier than I thought it would be!
Thanks Allan for your quick help.
bo