i have 3 tables as below;

table a
-------
EmpNo EmpName
----- -------
380 khalis
400 rashid

table b
-------
EmpNo IncCode Amount
----- ------- ------
380 111 100
380 222 150
400 111 50

table c
-------
EmpNo DedCode Amount
----- ------- ------
380 001 25
400 001 50
400 002 35

how to join all the above table to get the result like below;

EmpNo EmpName IncCode Amount DedCode Amount
----- ------- ------- ------ ------- ------
380 khalis 111 100 001 25
380 khalis 222 150 <null> <null>
400 rashid 111 50 001 50
400 rashid <null> <null> 002 35

i hope you guys can help me to cater the above problem. your assistance are very highly appreciate.

this should work

SELECT
a.EmpNo,
a.EmpName,
b.IncCode,
b.Amount,
c.DedCode,
c.Amount
From [table a] a
LEFT OUTER JOIN [table b] b ON a.EmpNo = b.EmpNo
LEFT OUTER JOIN [table c] c ON a.EmpNo = c.EmpNo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========

I think u have to add one more field to [table b] and[table c] to indicate the record no in particular employee in that table.

Now format of table b should be -

table b
-------
id EmpNo IncCode Amount
----- ------- ------
1 380 111 100
2 380 222 150
1 400 111 50

and format of table c will be -

table c
-------
id EmpNo DedCode Amount
----- ------- ------
1 380 001 25
1 400 001 50
2 400 002 35

And Run this Query for your required Result -

SELECT M.EMPNO,A.EMPNAME,B.INCCODE,B.AMOUNT,C.DEDCODE,C.A MOUNT
FROM
(
SELECT ID,EMPNO FROM TABLE_B
UNION
SELECT ID,EMPNO FROM TABLE_C
) M
LEFT OUTER JOIN TABLE_A A ON M.EMPNO=A.EMPNO
LEFT OUTER JOIN TABLE_B B ON M.ID=B.ID AND M.EMPNO=B.EMPNO
LEFT OUTER JOIN TABLE_C C ON M.ID=C.ID AND M.EMPNO=C.EMPNO
ORDER BY 1