mysql - 从任一表中选择值

Question

我使用 mySQL，并尝试查找一个表或另一个表中的行。以下是表及其字段:

user: id(PK), email, password, is_teacher

teacher: id, no_teacher(PK), name, id_group, no_class
teacher_information:no_teacher, cell_phone, SSN, email 

student: id, no_student(PK), name, id_group, no_class
student_information:cell_phone, email, grade, no_student

user_role: id, id_role(PK)

group_leader: id_group, id_role
group_member: id_group, id_role

group: id_group(PK), name 

每个用户(教师或学生)都可以是组中的领导者或成员(不能同时是两者)，并且任何用户都可以属于不同的组。我想要列出参与特定组的每个人的姓名和电子邮件。任何人都可以帮我获得这种情况下的sql查询吗？

Answer 1

如果将字段teacher_id添加到teacher_information表，并将student_id添加到student_information字段

SELECT
    *
FROM    
    (SELECT 
        t.name as `name`,
        ti.email as `email`,
        g.name as `group_name`,
        g.id_group as `id_group`
    FROM 
        teacher t, 
        teacher_information ti, 
        group g
    WHERE
        t.id = ti.teacher_id and
        g.id_group = t.id_group
    UNION
    SELECT
        s.name as `name`,
        si.email as `email`,
        g.name as `group_name`,
        g.id_group as `id_group`
    FROM 
        student s, 
        student_information si, 
        group g
    WHERE
        s.id = si.student_id and
        g.id_group = s.id_group
    ) as a
where
    a.id_group = ?
;