php - MySql : Get user details using nested relational table -

show below tables users, stu_master, stu_info, emp_master , emp_info.

users

stu_master

stu_master_user_id = users.user_id

stu_info

stu_info_id = stu_master.stu_master_stu_info_id

emp_master

em_user_id = users.user_id

emp_info

emp_info_id = emp_master.em_emp_info_id

i have user first name or last name using above 5 tables single query;

i have main 2 types of user 1 student , employee login details store in single table name users. store first/last name related details store in 2 different table based on user type. if user type s. first/last name in stu_info using stu_master table because user_id relation available in stu_master table other wise user type e, first/last name in emp_info using emp_master table because user_id relation in emp_master table.

my table relations :

• for student = users->stu_master->stu_info,
• for employee = users->emp_master->emp_info,

i have try below query not expected results.

select user_id userid, stu_first_name firstname, stu_last_name lastname, user_type usertype `users`       left join stu_master tsm on (stu_master_user_id = user_id , `user_type` = 's')     left join stu_info tsi on (tsm.stu_master_stu_info_id = tsi.stu_info_id)  union  select user_id userid, emp_first_name firstname, emp_last_name lastname, user_type usertype `users`       left join emp_master tem on (em_user_id = user_id , `user_type` = 'e')     left join emp_info tei on (tem.em_id = tei.emp_info_id)  `user_id` in (1, 2, 3, 4, 5, 6); 

the above query return all student , all employee. show below output

i first/last name given id (1, 2, 3, 4, 5, 6) using single query.

please give me type of single query achieve requirement or modified above query.

you close, , don't need union.

here's 3 ways 1 using case statement , 1 using nvl(), , 1 using inline view (if restricted changing table structures, use inline view, 'should' preform best. however, if list of users want grab sufficiently large, may want add additionally copies of clause inside each select statement of inline view).

select `user_id` userid , (case when `user_type` = 's' `stu_first_name`  when `user_type` = 'e' `emp_first_name`   end) firstname , (case when `user_type` = 's' `stu_last_name`        when `user_type` = 'e' `emp_last_name`    end) lastname , user_type usertype  `users`   left join stu_master tsm on (stu_master_user_id = user_id , `user_type` = 's') left join stu_info tsi on (tsm.stu_master_stu_info_id = tsi.stu_info_id) left join emp_master tem on (em_user_id = user_id , `user_type` = 'e') left join emp_info tei on (tem.em_id = tei.emp_info_id) `user_id` in (1, 2, 3, 4, 5, 6);    select `user_id` userid , nvl(`stu_first_name`,`emp_first_name`) firstname , nvl(`stu_last_name`,`emp_last_name`) lastname , user_type usertype  `users`   left join stu_master tsm on (stu_master_user_id = user_id , `user_type` = 's') left join stu_info tsi on (tsm.stu_master_stu_info_id = tsi.stu_info_id) left join emp_master tem on (em_user_id = user_id , `user_type` = 'e') left join emp_info tei on (tem.em_id = tei.emp_info_id) `user_id` in (1, 2, 3, 4, 5, 6);  select `user_id` userid , `firstname` firstname , `lastname`  lastname , `user_type` usertype  `users`   left join ( select 's' user_type , stu_master_user_id user_id , stu_first_name firstname , stu_last_name  lastname stu_master tsm inner join stu_info tsi on (tsm.stu_master_stu_info_id = tsi.stu_info_id) union select 'e' user_type , em_user_id user_id , emp_first_name firstname , emp_last_name  lastname emp_master tem  inner join emp_info tei on (tem.em_id = tei.emp_info_id) ) `user_info` using (user_id, user_type) `user_id` in (1, 2, 3, 4, 5, 6); ; 

below code used create dummy tables in sqlfiddle.com

create table users     (`user_id` int, `user_type` varchar(1)) ; insert users values (1,'a'); insert users values (2,'e'); insert users values (3,'e'); insert users values (4,'s'); insert users values (5,'s'); insert users values (6,'s');  create table stu_master     (`stu_master_id` int,`stu_master_stu_info_id` int,`stu_master_user_id` int) ;  insert stu_master values (1,1,4); insert stu_master values (2,2,5); insert stu_master values (3,3,6); insert stu_master values (4,4,7); insert stu_master values (5,5,8);  create table stu_info     (`stu_info_id` int,`stu_first_name` varchar(10),`stu_last_name` varchar(10)) ;  insert stu_info values (1,'student','one'); insert stu_info values (2,'student','two'); insert stu_info values (3,'student','three'); insert stu_info values (4,'student','four'); insert stu_info values (5,'student','five');   create table emp_master     (`em_id` int,`em_emp_info_id` int,`em_user_id` int) ; insert emp_master values (1,1,2); insert emp_master values (2,2,3); insert emp_master values (3,3,736); insert emp_master values (4,4,737); insert emp_master values (5,5,738);  create table emp_info     (`emp_info_id` int,`emp_first_name` varchar(10),`emp_last_name` varchar(10)) ; insert emp_info values (1,'employee','one'); insert emp_info values (2,'employee','two'); insert emp_info values (3,'employee','three'); insert emp_info values (4,'employee','four'); insert emp_info values (5,'employee','five');