0

Suppose the following tables:

faculty:
+----+--------+
| id | headFK |
+----+--------+

department:
+----+----------+--------+
| id | facultyFK| headFK |
+----+----------+--------+

teacher:
+----+--------------+-------+
| id | departmentFK | chief |
+----+--------------+-------+

What is the best way to go through each row of teacher. Save some column values as variables (suppose: id, departmentFK) to use it in further Queries (Update to be specific)

My aim is to set up subordinations of teacher which is like this chain 'Faculty Head' > 'Department Head' > 'Teacher'

Formally it should look like:

FOR EACH ROW IN teacher
{
    Set curDepartment = Current.departmentFK;
    Set curTeacher = Current.id;
    Set curFaculty = (Query to get faculty);
    Set FacultyHead = (Query to get current faculty head by var curFaculty );
    Set departmentHead = (Query to get current department head by var curDepartment );

    (Query to update teacher.chief with conditions using variable acquired before)
}

Could you please tell the way (Procedures, Functions or ordinary queries) how can I do it because I've never had experience with procedures or local variables in MySQL and I cannot figure out how can I implement it

PS: I'm not asking to write code (that would be too much I think) but an approach to implement it

Update

Thanks to the Rick James's note I came up with the following solution:

UPDATE teacher
    JOIN department ON teacher.DepFR = department.DepPK
    JOIN faculty ON department.FacFK = faculty.FacPK
SET teacher.ChiefFK = CASE
    WHEN teacher.TchPK = faculty.DeanFK THEN NULL
    WHEN teacher.TchPK = department.HeadFK THEN faculty.DeanFK
    ELSE department.HeadFK
END;

Is it a good way of doing it?

4
  • Is there a head table (that these headFK are referencing)? Oct 19 '15 at 6:12
  • @ypercube, headFK is referencing teacher table. So as chief
    – Eugene
    Oct 19 '15 at 15:01
  • Do you have to do it in SQL? Procedural code is much simpler to write in some procedural language.
    – jkavalik
    Oct 19 '15 at 20:26
  • @jkavalik Yes. I must do it in SQL (MySQL). And efficiency does not matter in this case
    – Eugene
    Oct 21 '15 at 16:46
0

It is a no-no to have redundant data.

Instead, use JOIN when doing the SELECT; this will let you dynamically get the faculty, faculty_head, etc.

Learn about JOIN.

5
  • But in the end I will need to update the record in teacher. And this update will depend on the head of faculty, head of department. Which means that I will need to compare specific teacher and heads of department and faculty. Will JOIN still be better to use?
    – Eugene
    Oct 21 '15 at 16:48
  • Eh? Is the teacher moving to a different department? Or some other action?
    – Rick James
    Oct 30 '15 at 15:34
  • Well, I have to update the teacher.chief so that: head of faculty is a chief for head of department; head of department is a chief for ordinary teacher
    – Eugene
    Nov 1 '15 at 11:39
  • A person can have many roles (teacher/chief/head/...)? So, you need a person table, plus roles table. roles could have person_id and role, and the pair of columns is the PK. (Or something in that direction.)
    – Rick James
    Nov 1 '15 at 15:55
  • I have to work with this structure (tables in question are trimmed to keep only relevant columns). The situation is the following: if teacher.id is set in faculty.headFK - this teacher is considered to be head of faculty; if teacher.id is set in department.headFK - this teacher is considered to be head of department. I updated my question and wrote a code which I came up to, thanks to your initial answer and this code works as I need it to.
    – Eugene
    Nov 1 '15 at 18:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.