mysql - Select rows according row type in another table -

this database structure.

member table(type - s: student, t: teacher)

+---+----+----+----+ |idx|type| id | pw | +---+----+----+----+ | 1 | s  |  | .. | | 2 | s  | b  | .. | | 3 | t  | c  | .. | | 4 | t  | d  | .. | | 5 | s  | e  | .. | | 6 | s  | f  | .. | +---+----+----+----+ 

student table

+---+-----+-----+------+ |idx|grade|class|number| +---+-----+-----+------+ | 3 |   3 |   8 |   29 | | 4 |   2 |  10 |   13 | +---+-----+-----+------+ 

teacher table

+---+-------+ |idx|enabled| +---+-------+ | 3 | n     | | 4 | n     | +---+-------+ 

i want member info specific info according member type.

my code is

$result = query("select * `member` `id` = '...' , `pw` = '...'"); $member_info = fetch_obj($result);  if ($member_info->type === 't') {     $result = query("select * `teacher` `idx` = '...'"); } else {     $result = query("select * `student` `idx` = '...'"); } $specific_info = fetch_obj($result); 

but, want data in 1 query request.
like:

set @type = select `type` `member` `member`.`idx` = '2';  select      case @type     when 's' `member`.*, `student`.*     when 't' `member`.*, `teacher`.*     end      case @type     when 's' `member`, `student`     when 't' `member`, `teacher`     end     `member`.`idx` = '2' ,     case @type     when 's'         `student`.`idx` = `member`.`idx`     when 't'         `teacher`.`idx` = `member`.`idx`     end ; 

how can do?

since appears in data idx column in member table represent both of student , teacher, double left-join. (and try never use select * )

select       m.idx,       m.type,       m.id,       m.pw,       case when s.idx null 0 else 1 end isstudent,       s.grade,       s.class,       s.number,       case when t.idx null 0 else 1 end isteacher,       t.enabled          member m          left join student s             on m.idx = s.idx          left join teacher t             on m.idx = t.idx          m.idx = 2 

then, in result set, have 2 "flag" (0 or 1) columns isstudent , isteacher conditionally use other columns if available.