CODEWITHSUNDEEP
mysqlsqlrecursion
fiona
fiona

Reputation: 89

Recursive table SQL

I´m new to SQL so I´m sorry if I make some simple mistakes. Having said that, I wanted to create a recursive table but I´m not sure how.

I have this table:

CREATE TABLE employee(
employee_id INT NOT NULL ,
Bdate DATETIME,
address VARCHAR(80),
PRIMARY KEY(supervisor_number)
);

And I have a 1:N cardinality in which 1 supervisor can supervise N supervisees. That´s why i need a recursive table, because an employee can either be a supervisor or a supervisee.

I don´t know how to create the recursive table so if anyone knows how I would be extremely grateful! If there´s anything that isn´t clear let me know.

Thanks!

Upvotes: 0

Views: 86

Answers (2)

Akina
Akina

Reputation: 42622

Possible realization.

The table

CREATE TABLE employee(
employee_id INT NOT NULL ,
Bdate DATETIME,
address VARCHAR(80),
PRIMARY KEY(employee_id),
supervisor_id INT,
FOREIGN KEY (supervisor_id) REFERENCES employee(employee_id) 
        ON DELETE SET NULL ON UPDATE CASCADE );

The triggers which checks for subordinates amount (the value of 3 is hardcoded)

CREATE TRIGGER tr_bi_check_subordinates_amount
BEFORE INSERT
ON employee
FOR EACH ROW
BEGIN
IF 3 <= ( SELECT COUNT(*)
          FROM employee
          WHERE supervisor_id = NEW.supervisor_id ) THEN
    SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'No available slots for specified supervisor';
END IF;
END

CREATE TRIGGER tr_bu_check_subordinates_amount
BEFORE UPDATE
ON employee
FOR EACH ROW
BEGIN
IF 3 <= ( SELECT COUNT(*)
          FROM employee
          WHERE supervisor_id = NEW.supervisor_id ) THEN
    SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'No available slots for specified supervisor';
END IF;
END

DEMO fiddle

Upvotes: 0

ktaria
ktaria

Reputation: 461

create a table of relation , you can call it supervision;

it the new table , put 2 fields : supervisor and supervisee; these two fields are foreign keys to the table Employee ;

for example, employee number 1 is a supervisor for the employee 2 and 3, you insert two lines in the new table :

insert into supervision (supervisor, supervisee) values (1, 2), (1, 3);

Upvotes: 1

Related Questions