MySQL join only when condition is met one time

Question

There probably is a simple solution but I can't wrap my head around it.

Let's say we have 4 tables persons (id, is_teacher) persons_relation (id, person_id, teacher_id) rooms_relation (id, person_id, room_id) rooms (id) (not used here)

persons can be either teachers or students. if a person is a teacher they can have multiple students assigned via persons_relation. a person can be assigned to many rooms, directly or through their teacher (so person->room or person(no teacher)->person(teacher)->room).

I want to find the persons that ARE NOT assigned to ANY room, neither directly nor via a teacher who has a room assigned. But my query also returns persons who are assigned to teachers of which some have rooms and some don't. So if one of those teachers has a room assigned I don't want the student returned here.

SELECT * FROM persons p
LEFT JOIN rooms_relation r ON r.person_id = p.id
LEFT JOIN persons_relation pr ON pr.person_id = p.id
LEFT JOIN rooms_relation tr ON tr.person_id = pr.teacher_id
WHERE tr.id IS NULL AND r.id IS NULL;

Example:

person 1 is student
persons 7,8,9 are teachers
student is assigned to teachers 7, 8, and 9 (via persons_relation)
student has no room assigned
teachers 7 and 9 have rooms assigned to them (via rooms_relation)
teacher 8 has no room assigned (this is the problem)

Since student has at least one room assigned via teachers 7 and 9 he should not be returned. But since teacher 8 has no room assigned, there is a row where tr.id IS NULL so he gets returned. I don't want that.

(I'm aware that I could've used only one join for rooms but I )

You must aggregate over a student and count the amount of rooms. If it is positive then the row should not be returned. — Akina, Jun 08 '21 at 18:19

score 0 · Answer 1 · answered Jun 08 '21 at 18:32

I want to find the persons that ARE NOT assigned to ANY room

The following query will list out the persons who are not in rooms_relation table. Isnt it that you want ?

 SELECT 
        * 
    FROM 
        `persons` 
    WHERE 
        `id` NOT IN (
            SELECT 
                DISTINCT(person_id) 
            FROM 
                `rooms_relation`
        )

score 0 · Answer 2 · answered Jun 08 '21 at 22:28

I think I solved it. Since the number of one person row is more than 1 if there is ANY relation I just had to add this condition.

SELECT * FROM persons p
LEFT JOIN rooms_relation r ON r.person_id = p.id
LEFT JOIN persons_relation pr ON pr.person_id = p.id
LEFT JOIN rooms_relation tr ON tr.person_id = pr.teacher_id
WHERE tr.id IS NULL AND r.id IS NULL 
GROUP BY p.id
HAVING count(p.id) = 1;

MySQL join only when condition is met one time

2 Answers2