Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
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
Comments
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: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
Comments
Rai Pawan at 10-Jan-13 4:35am
   
My 5 for your answer.
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: bad
good
Please Sign up or sign in to vote.

Solution 4

How about pivot table?
 
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 Wink | ;)
  Permalink  
v2
Comments
damodara naidu betha at 23-Jan-13 1:23am
   
Nice..
Maciej Los at 29-Jan-13 15:57pm
   
Thank you ;)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi
 
Your SQL QUERY is ...........
 

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)
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web03 | 2.8.140926.1 | Last Updated 14 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100