在mysql中要查绚的结果由两个表构成，这两个表中有一个表是不确定的，要根据查出的条件判断连接某个表

Question

共有四个表
topic user teacher student

CREATE TABLE student(
student_id INT AUTO_INCREMENT NOT NULL,
student_name VARCHAR(80) NOT NULL,
student_number VARCHAR(20) NOT NULL,
student_photopath VARCHAR(50) ,
PRIMARY KEY(student_id)
);

    CREATE TABLE  teacher(
teacher_id                          INT AUTO_INCREMENT NOT NULL,
teacher_name                    VARCHAR(80) NOT NULL,
teacher_number                VARCHAR(80) UNIQUE NOT NULL,         
teacher_photopath            VARCHAR(50) ,
PRIMARY KEY(teacher_id)   

);

CREATE TABLE user(
user_id INT AUTO_INCREMENT NOT NULL,
target ENUM('1','0') NOT NULL, -- 身份标识（学生：1 老师：0）
user_number VARCHAR(80) UNIQUE NOT NULL,--学生或老师的账号

PRIMARY KEY(userlogin_id)

)

CREATE TABLE topic(
topic_id INT AUTO_INCREMENT NOT NULL,
topic_title VARCHAR(300) NOT NULL, -- 话题标题
topic_content TEXT NOT NULL, -- 话题内容
user_number VARCHAR(20) NOT NULL, -- 话题创建人账号
PRIMARY KEY(topic_id)
) ;

我的需求是这样的要查询 topic_id，topic_title，topic_content ， user_number
,user_name,user_photopath(user_name和user_photopath,是student或者teacher表中的)，需要根据user表中的target判断一下具体去哪个表中查，这样的查询语句怎么写。不知道我说清楚了没，这个问题困惑了好久，求大神解答

Answer 1

select * from (
  select topic_id，topic_title，topic_content ， user_number
,student_name as user_name,student_photopath as user_photopath from topic,student,user where topic.user_number=student.student_number
and student.student_number=user.user_number and user.target=1
union all
  select topic_id，topic_title，topic_content ， user_number
teacher_name as user_name,teacher_photopath as user_photopath from topic,teacher,user where topic.user_number=teacher.teacher_number
and teacher.teacher_number=user.user_number and user.target=0
) order by topic_id