Joining on a common table, how do you get a FULL OUTER JOIN to expand on another table?
- by stimpy77
I've scoured StackOverflow and Google for an answer to this problem.
I'm trying to create a Microsot SQL Server 2008 view. Not a stored procedure. Not a function. Just a query (i.e. a view).
I have three tables. The first table defines a common key, let's say "CompanyID". The other two tables have a sometimes-common field, let's say "EmployeeName".
I want a single table result that, when my WHERE clause says "WHERE CompanyID = 12" looks like this:
CompanyID | TableA | TableB
12 | John Doe | John Doe
12 | Betty Sue | NULL
12 | NULL | Billy Bob
I've tried a FULL OUTER JOIN that looks like this:
SELECT Company.CompanyID,
TableA.EmployeeName,
TableB.EmployeeName
FROM Company
FULL OUTER JOIN TableA ON Company.CompanyID = TableA.CompanyID
FULL OUTER JOIN TableB ON
Company.CompanyID = TableB.CompanyID AND
(TableA.EmployeeName IS NULL OR TableB.EmployeeName IS NULL OR TableB.EmployeeName = TableA.EmployeeName)
I'm only getting the NULL from one matched table, I'm not getting the expansion for the other table. In the above sample, I'm basically only getting the first and third rows and not the second.
Can someone help me create this query and show me how this is done correctly?
BTW I already have a stored procedure that looks very clean and populates an in-memory table, but that isn't what I want.
Thanks.
