mysql - Need Sql Query to generate output as shown below tables -

here in example 3 tables used employee, employeework , employeeeducation.

employee table has 2 data rows.

employee has 6 data rows, 3 first employee , other 3 second employee.

finally employeeeducation has 4 data row, 2 first employee , 2 next employee.

i want output (if possible or alternative solution ) shown in last table output sample.

 employee +--------------------------+ |empid | empname | empphone| +--------------------------+ |1     | john    | 1234    | |2     | rick    | 5678    | +--------------------------+  employeework +--------------------------------------------+ |empwrkid | empid | departmentname | workyrs | +--------------------------------------------+ |1        | 1     | support        | 2       | |2        | 1     | development    | 3       | |3        | 1     | retail         | 5       | +--------------------------------------------+ |4        | 2     | support        | 1       | |5        | 2     | development    | 3       | |6        | 2     | retail         | 6       |  +--------------------------------------------+  employeeeducation +-----------------------------------------+ |empeduid | empid | eduname | division    | +-----------------------------------------+ |1        | 1     | inter   | first       | |2        | 1     | bachelor| second      | +-----------------------------------------+ |3        | 2     | bachelor| first       | |4        | 2     | masters | distinction | +-----------------------------------------+  can output below combining above 3 tables?  output sample +--------------------------------------------------------------------+ | empid | empname | departmentname | workyrs | eduname | division    | +--------------------------------------------------------------------+ | 1     | john    | support        | 2       | inter   | first       | | 1     | john    | development    | 3       | bachelor| second      | | 1     | john    | retail         | 5       | null    | null        | +--------------------------------------------------------------------+ | 2     | rick    | support        | 1       | bachelor| first       | | 2     | rick    | development    | 3       | masters | distinction | | 2     | rick    | retail         | 6       | null    | null        | +--------------------------------------------------------------------+ 

since there no actual relation between education , work, need create one, sample output appears though ordering id, each table can apply row_number() field join on:

/****************************************************************************************** generate tables , sample data ******************************************************************************************/ declare @employee table (empid int, empname varchar(50), empphone int); insert @employee values (1, 'john', 1234), (2, 'rick', 5678);  declare @employeework table (empwrkid int, empid int, departmentname varchar(50), workyrs int); insert @employeework values      (1, 1, 'support', 2), (2, 1, 'development', 3), (3, 1, 'retail', 5),      (4, 2, 'support', 1), (5, 2, 'development', 3), (6, 2, 'retail', 6)   declare @employeeeducation table (empeduid int, empid int, eduname varchar(50), division varchar(50)); insert @employeeeducation values     (1, 1, 'inter', 'first'), (2, 1, 'bachelor', 'second'),     (3, 2, 'bachelor', 'first'), (4, 2, 'masters', 'distinction');   /****************************************************************************************** actual query ******************************************************************************************/ emped (   select  empid,             eduname,             division,             rownumber = row_number() over(partition empid order empeduid)        @employeeeducation ), empwork (   select  empid,             departmentname,             workyrs,             rownumber = row_number() over(partition empid order empwrkid)        @employeework ) select  e.empid,         e.empname,         ee.departmentname,         ee.workyrs,         ee.eduname,         ee.division,         ee.rownumber,         empname2 = case when ee.rownumber = 1 e.empname else '-' end    @employee e         left join         (   select  empid = isnull(w.empid, e.empid),                     rownumber = isnull(w.rownumber, e.rownumber),                     w.departmentname,                     w.workyrs,                     e.eduname,                     e.division                empwork w                     full join emped e                         on e.empid = w.empid                         , e.rownumber = w.rownumber         ) ee             on ee.empid = e.empid order e.empid; 

i have left eduname null there no match, changing null values - job best left presentation layer, have left empid , empname repeating in each row, choosing whether or not display should again job presentation layer, have included column @ end empname2 shows how use rownumber column determine whether or not display column name.