thapark thapark - 6 months ago 7
SQL Question

Need Sql Query to generate output as shown below tables

Here in this example three tables are used Employee, EmployeeWork and EmployeeEducation.

Employee table has two data rows.

Employee has six data rows, three for first employee and other three for second employee.

Finally EmployeeEducation has four data row, two for first employee and two for next employee.

I want output (if possible or alternative solution ) as 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 |
+-----------------------------------------+

What can we do to get the output as below combining above three 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 |
+--------------------------------------------------------------------+

Answer

Since there is no actual relation between education and work, you need to create one, which from your sample output it appears as though you are simply ordering by ID, so for each table you can apply ROW_NUMBER() to get a field to join on:

/******************************************************************************************
GENERATE TABLES AND 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
******************************************************************************************/
WITH EmpEd AS
(   SELECT  EmpID,
            EduName,
            Division,
            RowNumber = ROW_NUMBER() OVER(PARTITION BY empID ORDER BY empEduID)
    FROM    @EmployeeEducation
), EmpWork AS
(   SELECT  EmpID,
            DepartmentName,
            WorkYrs,
            RowNumber = ROW_NUMBER() OVER(PARTITION BY empID ORDER BY empWrkId)
    FROM    @EmployeeWork
)
SELECT  e.EmpID,
        e.EmpName,
        ee.DepartmentName,
        ee.WorkYrs,
        ee.EduName,
        ee.Division,
        ee.RowNumber,
        EmpName2 = CASE WHEN ee.RowNumber = 1 THEN e.EmpName ELSE '-' END
FROM    @Employee AS e
        LEFT JOIN
        (   SELECT  EmpID = ISNULL(w.EmpID, e.EmpID),
                    RowNumber = ISNULL(w.RowNumber, e.RowNumber),
                    w.DepartmentName,
                    w.WorkYrs,
                    e.EduName,
                    e.Division
            FROM    EmpWork AS w
                    FULL JOIN EmpEd AS e
                        ON e.EmpID = w.EmpID
                        AND e.RowNumber = w.RowNumber
        ) AS ee
            ON ee.EmpID = e.EmpID
ORDER BY e.EmpID;

I have left EduName as NULL where there is no match, changing null values for - is probably a job best left to the presentation layer, similarly I have left empID and EmpName repeating in each row, as choosing whether or not to display this should again be a job for the presentation layer, I have however included a column at the end EmpName2 which shows how you could use the RowNumber column to determine whether or not to display the column name.