Rate this:
See more:
I have two table Table1

```LeaveCode
CL
EL
PL
KL
ML```

and Table2
```EmpCode	LeaveCode	Balances
1	CL	4
1	EL	3
1	KL	5
2	CL	2
2	EL	5
2	PL	2
2	ML	5
3	KL	2
3	ML	4
4	PL	2
5	CL	4
5	KL	4
5	PL	6
```

Now i want data so that all employees have all leavecode row and if data doesn't exist in Table2 for a particular employee then balance will be zero in resulting table.
Posted 9-Jan-13 19:56pm
Edited 9-Jan-13 21:42pm
v2
Shubhashish_Mandal at 10-Jan-13 2:27am

try "Full Join" .
rakesh@bbspl.com at 10-Jan-13 3:02am

Not giving the exact result.
Need help

Rate this:

## Solution 3

this way...
Note Temp1 leavetable and temp2 is employeetable
```with a as
(
Select distinct T1.LeaveCode,EmpCode
from Temp1 T1
cross Join Temp2 T2
)
select T1.LeaveCode,T1.EmpCode,isnull(balance,0) as balance
from a as T1
left join Temp2 T2 ON T1.LeaveCode=T2.FK_LeaveCode and T1.EmpCode=T2.EmpCode
order by T1.EmpCode```

Trial...
```Create table Temp1(LeaveCode VarChar(2) Primary Key)

Insert into Temp1 Values('AL')
Insert into Temp1 Values('BL')
Insert into Temp1 Values('CL')
Insert into Temp1 Values('DL')
Insert into Temp1 Values('EL')

Create table Temp2(EmpCode Int,FK_LeaveCode VarChar(2) references Temp1(LeaveCode),Balance Int)

Insert into Temp2 Values(1,'AL',3)
Insert into Temp2 Values(1,'BL',2)
Insert into Temp2 Values(1,'CL',1)

Insert into Temp2 Values(2,'AL',12)
Insert into Temp2 Values(2,'BL',12)
Insert into Temp2 Values(2,'CL',7)
Insert into Temp2 Values(2,'EL',8)

Insert into Temp2 Values(3,'DL',9)
Insert into Temp2 Values(3,'AL',22)

Insert into Temp2 Values(4,'CL',13)
Insert into Temp2 Values(4,'EL',14)
Insert into Temp2 Values(4,'DL',1);

with a as
(
Select distinct T1.LeaveCode,EmpCode from Temp1 T1
cross Join Temp2 T2
)
select T1.LeaveCode,T1.EmpCode,isnull(balance,0) as balance from a as T1
left join Temp2 T2 ON T1.LeaveCode=T2.FK_LeaveCode and T1.EmpCode=T2.EmpCode
order by T1.EmpCode

Drop Table Temp2
Drop Table Temp1```
Rai Pawan at 10-Jan-13 4:35am

Rakesh my earlier answer was wrong, as I did not properly take in to care in which
table did the balance and EmpCode existed.
- Pawan
Aarti Meswania at 10-Jan-13 4:54am

Thank you! :)
damodara naidu betha at 23-Jan-13 1:12am

5+
Aarti Meswania at 23-Jan-13 1:14am

Thank you!:)
Rate this:

## Solution 4

```DECLARE @cols NVARCHAR(200)
DECLARE @dt NVARCHAR(1800)
DECLARE @pt NVARCHAR(4000)

SET @cols = STUFF(( SELECT DISTINCT '],[' + [LeaveCode]
FROM Table1
ORDER BY '],[' + [LeaveCode]
FOR XML PATH('')), 1, 2, '') + ']'

SET @dt = 'SELECT EmpCode, LeaveCode, COALESCE(Balances,0) AS Balances' +
'FROM Table2 RIGHT OUTER JOIN Table1 ON Table2.[LeaveCodeId] = Table1.[LeaveCodeId] '

SET @pt = 'SELECT Empcode, ' + @cols + ' ' +
'FROM (' + @dt + ') AS DT' +
'PIVOT(MAX(Balances) FOR EmpCode IN(' + @cols + ')) AS PT ' +
'ORDER BY EmpCode'

EXEC(@pt)
```
Result:
```EmpCode	CL      EL     KL     PL     ML
1       4	3	5     0      0
2	2	5       0     2      5
... and so on ```

As you can see, it's more readible
v2
damodara naidu betha at 23-Jan-13 1:23am

Nice..
Maciej Los at 29-Jan-13 15:57pm

Thank you ;)
Rate this:

## Solution 2

Hi

```SELECT tab.EMPLOYEE_ID , tab.LeaveCode , ISNULL(BALANCE,0)  FROM dbo.Tab_LeaveBAL (NOLOCK)
RIGHT JOIN
(SELECT TabEmp.EMPLOYEE_ID,TabLeave.LeaveCode    FROM dbo.Tab_Leave as TabLeave (NOLOCK)
CROSS JOIN (
SELECT EMPLOYEE_ID  FROM dbo.Tab_LeaveBAL (NOLOCK)
GROUP BY EMPLOYEE_ID ) as TabEmp  ) AS tab
ON tab.EMPLOYEE_ID = tab_LeaveBal.EMPLOYEE_ID and tab.LeaveCode  = tab_LeaveBal.LEAVE_ID```
Output

```EMPLOYEE_ID LeaveCode BALANCE
----------- --------- -----------
1           CL        4
1           EL        3
1           PL        0
1           KL        5
1           ML        0
2           CL        2
2           EL        5
2           PL        2
2           KL        0
2           ML        5
3           CL        0
3           EL        0
3           PL        0
3           KL        2
3           ML        4
4           CL        0
4           EL        0
4           PL        2
4           KL        0
4           ML        0
5           CL        4
5           EL        0
5           PL        6
5           KL        4
5           ML        0```

(25 row(s) affected)
v2