SQL Query-need to use 1 field to pull info from 2 other fields based on info in 2 fields in another table

Ok, I've wracked my brain for weeks and I can't seem to get this to work. I have 2 tables, the first we'll call Research, the second Employees. In Research table there are 2 fields, CreatedBy and AssignedTo, which both contain int values corresponding to an EmployeeID in the Employees table. The Employees Table has the EmployeeID, FirstName, and LastName fields which correspond to each ID. For the page I'm creating the CreatedBy needs to be translated to the person's First and Last Name and have that displayed, while the AssignedTo needs to be displayed in the same way for each record in the Research Database. Example:

Research Table
ID CreatedBy AssignedTo
1..1112......2221
2..1113......3332
3..1112......<NULL>

Employees Table
EmployeeID  FirstName  LastName
1112........Bob........Smith
1113........Jane.......Doe
2221........Peter......Cottontail
3332........Snow.......White

The page needs to display
ID....CreatedBy....AssignedTo
1.....Bob Smith....Peter Cottontail
2.....JaneDoe......Snow White
3.....Bob Smith....Unassigned

I'm having a beast of a time getting this to work. I can get it to show Unassigned if the field value is null. I can get CreatedBy OR AssignedTo to show up properly, but not both. How can I get the info in the Employees table to show up based on the value in CreatedBy, then adjust itself to show up based on the value in AssignedTo? Any help would be appreciated.Start Free Trial