datagrid sql data source join

Question

I have a table called Leaves. Fields are,

* LeaveID
* PersonID
* ActingPersonID

In Person's table, I have,

* PersonalID
* EmployeeCode
* PersonName

Now, in the datagrid, I need to show,

* PersonEmployeeCode
* PersonName
* ActingPersonEmployeeCode
* ActingPersonName

Now, I know how to write the SQL Join to get the data populated. But, the problem is, in some cases, there won't be any acting person. In which case, I need to display data,

* PersonEmployeeCode = 0001
* PersonName = John
* ActingPersonEmployeeCode = -
* ActingPersonName = No acting Person

Now, I am stuck here. How do I do a conditional join statement?

I really need a quick help on this...

Regards

Kush

Database coding

SELECT dbo.LeavesManager.leaveID, dbo.LeavesManager.PersonalID, dbo.PersonalDetails.EMPNO, dbo.PersonalDetails.NAMIN, dbo.LeavesManager.actingPersonalID FROM dbo.LeavesManager INNER JOIN dbo.PersonalDetails ON dbo.LeavesManager.PersonalID = dbo.PersonalDetails.PersonalID

Returns, 1 25 ACA01 Neranjan Manoj 26

2 25 ACA01 Neranjan Manoj 0

3 26 ACA06 A. N. M. K. Waruni 0

SELECT dbo.LeavesManager.leaveID, dbo.LeavesManager.PersonalID, dbo.PersonalDetails.EMPNO, dbo.PersonalDetails.NAMIN, dbo.LeavesManager.actingPersonalID, PersonalDetails_1.EMPNO AS Expr1, PersonalDetails_1.DESIG FROM dbo.LeavesManager INNER JOIN dbo.PersonalDetails ON dbo.LeavesManager.PersonalID = dbo.PersonalDetails.PersonalID INNER JOIN dbo.PersonalDetails AS PersonalDetails_1 ON dbo.LeavesManager.actingPersonalID = PersonalDetails_1.PersonalID

Returns, 1 25 ACA01 Neranjan Manoj 26 ACA06 TELE-Arcade Staff

When there is no acting person on behalf of the person who applies the leave, the user selects default value from the combobox where ActingPersonEmployeeCode = 0 & ActingPersonName = "No acting Person".

Now the problem is there no actual person in the PersonalDetails table. So, the records don't populate for ActingPersonEmployeeCode = 0.

Answer 1

Got the answer

SELECT     dbo.LeavesManager.leaveID, dbo.LeavesManager.PersonalID, dbo.PersonalDetails.EMPNO, dbo.PersonalDetails.NAMIN, dbo.LeavesManager.actingPersonalID, 
                      PersonalDetails_1.EMPNO AS Expr1, PersonalDetails_1.DESIG
FROM         dbo.LeavesManager **LEFT OUTER JOIN**
                      dbo.PersonalDetails ON dbo.LeavesManager.PersonalID = dbo.PersonalDetails.PersonalID **LEFT OUTER JOIN**

                      dbo.PersonalDetails AS PersonalDetails_1 ON dbo.LeavesManager.actingPersonalID = PersonalDetails_1.PersonalID