how do i get data from two tables like this?

Question

3.00/5 (1 vote)

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

rakesh@bbspl.com

Updated 9-Jan-13 21:42pm

Aarti Meswania

v2

Add a Solution

Comments

Shubhashish_Mandal 10-Jan-13 2:27am

try "Full Join" .

rakesh@bbspl.com 10-Jan-13 3:02am

Not giving the exact result.
Need help

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Aarti Meswania · Answer 1 · 2013-01-09T22:02:00

this way...
Note Temp1 leavetable and temp2 is employeetable

SQL

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...

SQL

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

Maciej Los · Answer 2 · 2013-01-12T11:47:00

How about pivot table?

SQL

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 ;)

P.S Vijay · Answer 3 · 2013-01-09T21:42:00

Hi

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

SQL

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)

how do i get data from two tables like this?

3 solutions

Solution 3

Solution 4

Solution 2

Add your solution here

Preview 0