datagrid sql data source join

I have a table called Leaves. Fields are,

* LeaveID
* PersonID
* ActingPersonID

In Person's table, I have,

* PersonalID
* EmployeeCode
* Per开发者_如何学编程sonName

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

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.

---

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