Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004

    Arrow Unanswered: Recursive Relationship

    Alright... I'm making a very simple database that'll keep track of employess and who their supervisors are. Since Supervisors are employees as well, they all fall into the same table...

    Now, the table has 3 main rows, EmployeeID, Name, and SupID.
    EmployeeID is an auto-incrementing Integer starting at 0
    SupID is a foreign key from the EmployeeID. Since all Sups are also employess, it becomes slightly recursive.

    Now the logic behind setting it up is not a problem whatsoever. The problem is, how do I make the relationship?

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    make the relationship?

    create table Employees
    ( EmployeeID integer not null primary key auto_increment
    , Name varchar(99) not null
    , SupID integer null
    , foreign key ( SupID ) references Employees ( EmployeeID )
    note that mysql does not support foreign keys unless you use innodb tables | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    hi if not ever emp has a sup o even has 2 of em you should handle this in two tables

    - id (primary auto inc)
    - name
    - position ......

    - emp (emp id) (key)
    - sup (emp id) (key)

    so there is the possibility to add two or more rows for one employee without saving the employe data twice

    to select an emps sups just do this
    SELECT fields FROM emp, emp_sup WHERE = emp_sup.sup AND emp_sup.emp = user_to_seach

    to select a sups emps just do this
    SELECT fields FROM emp, emp_sup WHERE = emp_sup.emp AND emp_sup.sup = supervisor

    hope this usefull a bit

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts