asked
Join another table to full outer join results from cte
I've had a request for a query that combines information from two tables. For the result I need not only the records which link, but also the records from each table which do not have a corresponding link in the other table. (The user wants this to use with PowerPivot.) I've come up with a query which gives me the results EXCEPT for a couple of entries which have a NULL where I'm expecting a name. As you can see, I've used a couple of CTEs to get the data I need from each table. I used the employee id in those, and then joined the personnel table at the last select in order to get the name. Can this be corrected to give me all the names for the ids? Is there another way to get this information? Or is it impossible, as I've been told? Here is the query: with A as (select tk_empl_uno [empl_uno], month, sum(base_hrs)[Actual Hrs],sum(base_amt)[Actual Amt] from tah_summary_tr tah where tah.month = '201001'and (TIME_CLASS <> 'n') group by tk_empl_uno, month), B as (select empl_uno,period,month, bud.BILLABLE_HRS [Budget Hrs] from dbo.TBM_PERSNL_BUD bud WHERE bud.PERIOD = '201001' ) select p.employee_code, p.employee_name,A.month,B.period,A.[actual hrs],A.[actual amt],B.[budget hrs] from hbm_persnl p join A on p.empl_uno = a.empl_uno full outer join B on b.empl_uno = p.empl_uno order by b.period,p.employee_name;
I'm not sure I've made myself clear. Each CTE will return a column with an employee id. However, there may be employees in each result set that are not in both result sets. I need all of the results, and would like to have the name instead of the uno, as the uno doesn't relate to anything the user would recognize. I thought joining on the personnel table after I got the results would give me that. Instead, I get the employee name for all the results which are in A, whether or not they have any matching entries in B, but the results from B show NULL instead of the employee name. code name month period hrs amt budget 175 Baggins 201001 NULL 7.5 412.5 NULL 603 Vader 201001 NULL 0.4 22 NULL NULL NULL NULL 201001 NULL NULL 41.63 2038 Public 201001 201001 160.95 16095.0 158.33 627 Potter 201001 201001 155.6 20110. 150
Try [ISNULL][1]. eg: SELECT ISNULL(foo, bar) will return `bar` if `foo` is null, and `foo` otherwise. So, in terms that might be a bit closer to your requirement: SELECT ISNULL(b.empname, a.empname) will return the `empname` field from table `a` if the `empname` field in `b` is `NULL` [1]:
http://msdn.microsoft.com/en-us/library/ms184325.aspx
If it's just the odd field that's missing, then it could be the way to go... if it's whole rows missing, then you'll be needing the correct join.
Here's an example which re-worked your query using some sample data. I created temporary tables instead of using the table names in your CTE, but this should give you a pretty good idea of what's going on regardless. create table #a ( tk_empl_uno int, month int, base_hrs int, base_amt int, time_class char(1) ); create table #b ( empl_uno int, period int, month int, billable_hrs int ); create table #hbm_persnl ( employee_code int, employee_name varchar(30), empl_uno int ); insert into #hbm_persnl values(1, 'Bob', 1); insert into #hbm_persnl values(2, 'Ted', 2); insert into #hbm_persnl values(3, 'Sally', 3); insert into #hbm_persnl values(4, 'Diane', 4); insert into #a values(1, 1, 10, 10, 'T'); insert into #a values(2, 1, 40, 300, 'T'); insert into #b values (1, 1, 1, 150); insert into #b values (3, 1, 1, 30); insert into #b values (5, 1, 1, 60); with A as ( select tk_empl_uno [empl_uno], month, sum(base_hrs)[Actual Hrs], sum(base_amt)[Actual Amt] from #a tah where tah.month = 1 group by tk_empl_uno, month ), B as ( select empl_uno, period, month, bud.BILLABLE_HRS [Budget Hrs] from #b bud WHERE bud.PERIOD = 1 ) select p.employee_code, p.employee_name, A.month, B.period, A.[actual hrs], A.[actual amt], B.[budget hrs] from #hbm_persnl p /* This should be a left outer join. See the comments below. */ inner join A on p.empl_uno = a.empl_uno full outer join B on b.empl_uno = p.empl_uno where A.empl_uno IS NOT NULL or b.empl_uno IS NOT NULL order by b.period, p.employee_name; The data set I ginned up will show two distinct types of "missing record" pieces of information. The first is if you have a record in B but not in A _and_ the information exists in P. In that case, the query will end up returning NULL for the P information (employee code and employee name). The easiest way to correct this is to convert your **inner** join on A to a **left outer** join on A. In the example above, this is the record where the budget hours is 30. The second type of "missing information" problem is when you have an Employee UNO in expression B but no record in table P (the personnel table). In that case, you have bad data and unless the employee information is stored somewhere else, you won't be able to display it. In the example above, this is the one where the budget hours is 60. There is no employee UNO of 5 in the personnel table, so we're out of luck trying to display that information.
The first type of 'missing information' is exactly my situation. The difficulty with using the left outer join to P is that there are lots of records in P which aren't needed for this query.
Ah, okay. Your situation's still salvagable, then. :-) I was afraid that you had some out-and-out bad data in set B. I have updated my query to include a where clause to see if the person is in data set A or B. Then, using the test data, you won't see Diane (who was not in either A or B) but you will see everyone else. I don't believe that performance will be miserable with this, especially if P has an index which includes empl_uno. Also, if there are no empl_uno values in A or B which don't have corresponding matches in P, you could even change the full outer join to be a left outer join. I left it a full outer join because of the possibility of the second type of missing information.
Beautiful! I had to go with the other answer to get the user started on her project, but I came back to it and this is just what I needed. Thank you so much. That makes this my preferred answer now.
