CREATE TABLE #TableA
(
Project# INT PRIMARY KEY,
CaseManagerID CHAR(6),
ProjectManagerID CHAR(6)
);
CREATE TABLE #TableB
(
EmployeeID CHAR(6),
FirstName VARCHAR(20),
LastName VARCHAR(20)
);
SET NOCOUNT ON;
INSERT #TableA(Project#,FirstName,LastName)
SELECT 1,'AAAA11',NULL
UNION SELECT 2,'BBBB11',NULL
UNION SELECT 3,'CCCC11','DDDD11';
INSERT #TableB(EmployeeID,FirstName,LastName)
SELECT 'AAAA11','Joe','Smith'
UNION SELECT 'BBBB11','Mary','Best'
UNION SELECT 'CCCC11','Jim','Todd'
UNION SELECT 'DDDD11','Sam','Jones';
-- assuming FirstName and LastName can not be NULL
SELECT
Project#,
Managers = B.FirstName + ' ' + B.LastName +
COALESCE('/' + C.FirstName + ' ' + C.LastName, '')
FROM #TableA A
LEFT OUTER JOIN #TableB B
ON B.EmployeeID = A.CaseManagerID
LEFT OUTER JOIN #TableB C
ON C.EmployeeID = A.ProjectManagerID
ORDER BY 1;
DROP TABLE #TableA, #TableB;
I didn't make these tables. They are a part of an accounting system. I
am just trying to create a report based on them. I'll try explaining
myself again.
Here are my tables
Table A
Project # CaseManagerID ProjectManagerID
1 AAAA11 NULL
2 BBBB11 NULL
3 CCCC11 DDDD11
Table B
EmployeeID FirstName LastName
AAAA11 Joe Smith
BBBB11 Mary Best
CCCC11 Jim Todd
DDDD11 Sam Jones
I simply need a statement such as this one
Select Project#, CASE WHEN ProjectManagerID IS NOT NULL THEN
CaseManagerID + '/' +
ProjectManagerID ELSE CaseManagerID END AS Managers From Table A LEFT
OUTER JOIN Table B ON A.(Not sure what to put here) = B.EmployeeID".
However, instead of printing the IDs, I would like to print a first
and last name as can be seen below
Project # Managers
1 Joe Smith
2 Mary Best
3 Jim Todd/Sam Jones
Please let me know if any further clarification is required.