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;
Try [ISNULL]. 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` :
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.