x

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;
more ▼

asked Mar 23 '11 at 12:02 PM in Default

lostinaquery gravatar image

lostinaquery
46 3 3 4

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
Mar 23 '11 at 12:32 PM lostinaquery
(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

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
more ▼

answered Mar 23 '11 at 12:38 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.4k 14 20 44

I think I can make this work.
Mar 23 '11 at 01:08 PM lostinaquery
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.
Mar 23 '11 at 01:24 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 23 '11 at 12:26 PM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

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.
Mar 23 '11 at 12:59 PM lostinaquery

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.
Mar 23 '11 at 01:03 PM Kevin Feasel
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.
Mar 31 '11 at 12:43 PM lostinaquery
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x38
x11

asked: Mar 23 '11 at 12:02 PM

Seen: 2490 times

Last Updated: Mar 23 '11 at 12:04 PM