If I'm logged in as a teacher, I would like to get the student and the parents. If I'm logged in as a parent I would like to get the student and the teachers. Lastly, if I'm logged in as a student, I would like to get the teacher and parents

Here is how my table is set up

CREATE TABLE users(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);

INSERT INTO users (name)
VALUES ("Gregor (Student)"),
    ("Louise (Student)"),
    ("Liza (Teacher)"),
    ("Matt (Teacher)"),
    ("Bob (Parent)"),
    ("Wilma (Parent)"),
    ("Tim (Parent)");
    
CREATE TABLE school(
    student_id INT,
    teacher_id INT NULL,
    parent_id INT NULL,
    PRIMARY KEY (`student_id`, `teacher_id`, `parent_id`),
    FOREIGN KEY (`student_id`) REFERENCES `users` (`id`),
    FOREIGN KEY (`teacher_id`) REFERENCES `users` (`id`),
    FOREIGN KEY (`parent_id`) REFERENCES `users` (`id`)
);
INSERT INTO school (student_id, teacher_id, parent_id)
VALUES (1, 3, 5),
(1, 4, NULL),
(2, 3, 6),
(2, 4, 7);

My current query:

SELECT u.name AS student, t.name AS teacher, p.name AS parent FROM school s INNER JOIN users u ON (u.id = s.student_id) LEFT JOIN users t ON (t.id = s.teacher_id) LEFT JOIN users p ON (p.id = s.parent_id) WHERE (s.student_id = 1 OR s.teacher_id = 1 OR s.parent_id = 1);

What I get now:

student | teacher | parent
Gregor, |Liza, Matt|, Bob
Gregor, |Liza|, NULL

What I expect depend on which user is logged in.

So if I'm logged in as Bob, because Bob is only connected to one student:

student | teacher | parent
Gregor, |Liza, Matt|, Bob

if I'm logged in as Liza, because Liza is present in both students:

student | teacher | parent
Gregor, |Liza, Matt|, Bob
Louise, |Liza, Matt|, Wilma, Tim

CodePudding user response：

As you have multiple rows per student you need a GROUP BY and a GROUP_CONCAT to combine the rows that belong together

SELECT 
    u.name AS student, GROUP_CONCAT(t.name) AS teacher, GROUP_CONCAT(p.name) AS parent
FROM
    school s
        INNER JOIN
    users u ON (u.id = s.student_id)
        LEFT JOIN
    users t ON (t.id = s.teacher_id)
        LEFT JOIN
    users p ON (p.id = s.parent_id)
#WHERE
 #  (s.student_id = 1 OR s.teacher_id = 1
 #      OR s.parent_id = 1)
 GROUP BY student;

student          | teacher                       | parent                     
:--------------- | :---------------------------- | :--------------------------
Gregor (Student) | Liza (Teacher),Matt (Teacher) | Bob (Parent)               
Louise (Student) | Liza (Teacher),Matt (Teacher) | Wilma (Parent),Tim (Parent)

db<>fiddle here

here are sample how you would find someone by name

SELECT 
    u.name AS student, GROUP_CONCAT(t.name) AS teacher, GROUP_CONCAT(p.name) AS parent
FROM
    school s
        INNER JOIN
    users u ON (u.id = s.student_id)
        LEFT JOIN
    users t ON (t.id = s.teacher_id)
        LEFT JOIN
    users p ON (p.id = s.parent_id)
WHERE
   (u.name LIKE  'Liza%' 
   OR t.name LIKE  'Liza%'
       OR p.name LIKE 'Liza%'
       )
 GROUP BY student;

student          | teacher        | parent        
:--------------- | :------------- | :-------------
Gregor (Student) | Liza (Teacher) | Bob (Parent)  
Louise (Student) | Liza (Teacher) | Wilma (Parent)

SELECT 
    u.name AS student, GROUP_CONCAT(t.name) AS teacher, GROUP_CONCAT(p.name) AS parent
FROM
    school s
        INNER JOIN
    users u ON (u.id = s.student_id)
        LEFT JOIN
    users t ON (t.id = s.teacher_id)
        LEFT JOIN
    users p ON (p.id = s.parent_id)
WHERE
   (u.name LIKE  'Bob%' 
   OR t.name LIKE  'Bob%'
       OR p.name LIKE 'Bob%'
       )
 GROUP BY student;

student          | teacher        | parent      
:--------------- | :------------- | :-----------
Gregor (Student) | Liza (Teacher) | Bob (Parent)

db<>fiddle here