Sql Select Union

[RosieGp]

Hi,
i have 2 tables as following:
Employee_Info
ID Employee_Name Date_Started SickDays
1-1 Joe Mar 1 2
12-2 Randall Feb 2 5
12-5 Pam April 9 6

Employees_YearsOfWork
ID Years Date_Completed
1-1 2 Dec 12
12-2 7 Jun 15
12-5 15 Aug 11

In my results panel I have to show all the 6 entries above...
ID Employee_Name Date_Started SickDays Years Date_Complete

So i have the following query...
Select ID, Employee_Name, Date_Started, '' as Years, '' as Date_Completed
From Employee_Info
group by ID, Employee_Name, Date_Started
UNION
Select ID, '' as Employee_Name,'' as Date_Started, Years, Date_Completed
From Employees_YearsOfWork
group by ID, Years, Date_Completed


The results looks like the following:
ID Employee_Name Date_Started SickDays Years Date_Complete
1-1 Joe Mar 1 2
12-2 Randall Feb 2 5
12-5 Pam April 9 6
1-1 2 Dec 12
12-2 7 Jun 15
12-5 15 Aug 11

So I need to populate the rest of the empty feilds from each other... In the end I should end up will 6 rows like the follwoing:

ID Employee_Name Date_Started SickDays Years Date_Complete
1-1 Joe Mar 1 2 2 Dec 12
12-2 Randall Feb 2 5 7 Jun 15
12-5 Pam April 9 6 15 Aug 11
1-1 Joe Mar 1 2 2 Dec 12
12-2 Randall Feb 2 5 7 Jun 15
12-5 Pam April 9 6 15 Aug 11


Yes i need repititive data...
Thanks for your help...

 

[markros]

;with cte as (Select ID, Employee_Name, Date_Started, NULL as Years, NULL as Date_Completed, 1 as Record_Type 
       From Employee_Info
       group by ID, Employee_Name, Date_Started
       UNION
       Select ID, NULL as Employee_Name,NULL as Date_Started, Years, Date_Completed, 2 as Record_Type 
       From Employees_YearsOfWork
       group by ID, Years, Date_Completed)

select c.ID, coalesce(c.Employee_Name, c1.Employee_Name,c2.Employee_Name) as Employee_Name,
coalesce(c.Date_Started, c1.Date_Started, c2.Date_Started) as Date_Started, etc.
from cte c
LEFT JOIN cte c1 
ON c.ID = c1.ID and c2.Record_Type = 2
LEFT JOIN cte c2
on c.ID = c2.ID and c1.Record_Type = 1

From the top of my head - may need tweaking.

PluralSight Learning Library

 

[RosieGp]

I'm pretty sure you got what I need... But what is "cte", "coalesce" I don't seem to understand that..

So after I have my original query:

Select ID, Employee_Name, Date_Started, '' as Years, '' as Date_Completed
From Employee_Info
group by ID, Employee_Name, Date_Started
UNION
Select ID, '' as Employee_Name,'' as Date_Started, Years, Date_Completed
From Employees_YearsOfWork
group by ID, Years, Date_Completed
I add this part:

select c.ID, coalesce(c.Employee_Name, c1.Employee_Name,c2.Employee_Name) as Employee_Name,
coalesce(c.Date_Started, c1.Date_Started, c2.Date_Started) as Date_Started, etc.
from cte c
LEFT JOIN cte c1
ON c.ID = c1.ID and c2.Record_Type = 2
LEFT JOIN cte c2
on c.ID = c2.ID and c1.Record_Type = 1

Sorry, for not getting it right away...

 

[markros]

In your original query I added a Record_Type column (1 for first part and 2 for the second part of the union). Then I turned this into a common table expression (CTE for short). We could have used a temp table instead and the performance will most likely be better. After that I re-used this cte (temp table) 3 times in the query.

PluralSight Learning Library