Outer Joins are my nemisis 2

[Imakeoil]

Hello,

I am trying to join two tables and have the output include all eqmtids. The hist_eqmtlist table has all eqmtids but the hist_load table has only some. I need the output to include all eqmtids and nulls or zeros for the other columns. I thought an outer join would do it but alas, I have been stumped by a join again.

select hist_loads.shiftindex, eqmtid, sum (loadtons) as loadtons, sum (ex_tmcat01/3600)as oper, [name], excav
--into #t_loadex
from hist_eqmtlist
left outer join hist_loads on hist_loads.shiftindex = hist_eqmtlist.shiftindex
and hist_eqmtlist.eqmtid = hist_loads.excav
inner join enum_load_grp_type on hist_loads.[load] = enum_load_grp_type.num
WHERE hist_loads.shiftindex between @startd and @endd and Pdb.dbo.DefineEqmt(excav) = 'Ours'
and hist_loads.extraload = 0
Group by hist_loads.shiftindex, excav, [name], eqmtid
order by excav

So where am I going wrong? Is it because of the 'hist_eqmtlist.eqmtid = hist_loads.excav' bit? isn't that what an outer join is for?

Thanks for all the help

 

[gmmastros]

Change where to and and try again.

select    hist_loads.shiftindex, eqmtid, sum (loadtons) as loadtons, sum (ex_tmcat01/3600)as oper, [name], excav    
--into     #t_loadex
from        hist_eqmtlist
left outer join hist_loads on hist_loads.shiftindex = hist_eqmtlist.shiftindex  
and hist_eqmtlist.eqmtid = hist_loads.excav    
inner join enum_load_grp_type on hist_loads.[load] = enum_load_grp_type.num
[!]AND[/!]    hist_loads.shiftindex between @startd and @endd and Pdb.dbo.DefineEqmt(excav) = 'Ours'
        and hist_loads.extraload = 0 
Group by  hist_loads.shiftindex, excav, [name], eqmtid
order by  excav

If this works for you, let me know and I will explain WHY it works.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Imakeoil]

Hi George,

Thanks for the reply but this does not work, I am getting the same output. It is interesting though, I didn't realize that you could 'and' in a join without it comparing two things.

Thanks

 

[gmmastros]

I suppose I should have taken a closer look at the query before making my suggestion. Sorry.

I think I was headed in the right direction though. The problem (from my perspective) is that I don't necessarily have all of the information I need in order to make a 100% acceptable solution. Specifically, I see that you are calling a user defined function (DefineEqmt) with a column (excav). I have no way of knowing which table this column exists in.

Anyway... try this query:

select hist_loads.shiftindex, 
       eqmtid, 
       sum (loadtons) as loadtons, 
       sum (ex_tmcat01/3600)as oper, 
       [name], 
       excav    
--into     #t_loadex
from   hist_eqmtlist
       left outer join hist_loads 
         on hist_loads.shiftindex = hist_eqmtlist.shiftindex  
         and hist_eqmtlist.eqmtid = hist_loads.excav    
         and hist_loads.shiftindex between @startd and @endd 
         and hist_loads.extraload = 0 
         and Pdb.dbo.DefineEqmt(excav) = 'Ours'
       Left join enum_load_grp_type 
         on hist_loads.[load] = enum_load_grp_type.num
Group by  hist_loads.shiftindex, excav, [name], eqmtid
order by  excav

The important thing to realize is that I moved conditions from your original where clause in to the join clause. The problem is... when you use a left join, you normally get all the rows from the left table (hist_eqmtlist) and any matching columns in the right table (hist_loads). If there is no match, based on the join clause, you will still get the rows from the left table. The columns from the right table will have NULLS in them.

For example, if there is no matching row in the hist_loads table, the left join still returns the row from the hist_eqmtlist table. You'll also have the columns from the right table (hist_loads) but all the values will be NULL, including (for example) the extraload column. If you have a where clause condition for extraload = 0, but there's actually a value of NULL, that row will be filtered out. In essence, your left join query will behave as though it was an inner join, and you will not get the results you are expecting.

This query is made even more difficult by the fact you then inner join a 3rd table on a table that you originally left joined on. This too can cause your left join to behave as though it were an inner join. Let's look at the original condition for that join.

[tt][!]inner[/!] join enum_load_grp_type on [!]hist_loads.[load][/!] = enum_load_grp_type.num[/tt]

In order to get all of the rows in the original table (hist_eqmtlist), you need to preserve your left joins. Since you are later inner joining the enum_load_grp_type table to the table you had left joined, you are effectively making an inner join on the hist_loads table because any NULLS that would have been returned by the left join will not match the subsequent inner join and that row will not be returned.

As you can see, describing this situation is a bit difficult. This topic is extremely important. If you don't understand something, let me know and I will attempt a better explanation.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Here's an example of the left join to inner join problem. This is a made up example, but I hope it clearly illustrates the problem.

First, set up the data:

Declare @Recipe Table(RecipeId Int, FoodId Int)

Insert Into @Recipe Values(1, 100)
Insert Into @Recipe Values(1, 101)
Insert Into @Recipe Values(1, 102)

Declare @Food Table(FoodId Int, Description VarChar(20))
Insert Into @Food Values(100, 'Bread')
Insert Into @Food Values(101, 'Peanut Butter')

Declare @FoodColor Table(FoodId Int, Color VarChar(20))
Insert Into @FoodColor Values(100, 'White')
Insert Into @FoodColor Values(102, 'Red')

Now, suppose we want to get all of the recipe items and any matches that exist in the food table. We could write a query like this:

Declare @Recipe Table(RecipeId Int, FoodId Int)

Insert Into @Recipe Values(1, 100)
Insert Into @Recipe Values(1, 101)
Insert Into @Recipe Values(1, 102)

Declare @Food Table(FoodId Int, Description VarChar(20))
Insert Into @Food Values(100, 'Bread')
Insert Into @Food Values(101, 'Peanut Butter')

Declare @FoodColor Table(FoodId Int, Color VarChar(20))
Insert Into @FoodColor Values(100, 'White')
Insert Into @FoodColor Values(102, 'Red')

Select *
From   @Recipe R
       Left Join @Food F
         On R.FoodId = F.FoodId

The results are this:
[tt][blue]

RecipeId FoodId FoodId Description
----------- ----------- ----------- --------------------
1 100 100 Bread
1 101 101 Peanut Butter
1 102 NULL NULL
[/blue][/tt]

Notice that the recipe table has a row with foodid = 102, but that row does not exist in the food table. It's still included in the results because we used a left join. But also notice the 2nd FoodId column. This is actually the value from the "right" table (@Food). It contains a NULL. If we then inner join to another table on this NULL value, there will NOT be a match and the row will be removed from the results. Like this:

Set NOCOUNT ON
Declare @Recipe Table(RecipeId Int, FoodId Int)

Insert Into @Recipe Values(1, 100)
Insert Into @Recipe Values(1, 101)
Insert Into @Recipe Values(1, 102)

Declare @Food Table(FoodId Int, Description VarChar(20))
Insert Into @Food Values(100, 'Bread')
Insert Into @Food Values(101, 'Peanut Butter')

Declare @FoodColor Table(FoodId Int, Color VarChar(20))
Insert Into @FoodColor Values(100, 'White')
Insert Into @FoodColor Values(102, 'Red')

Select *
From   @Recipe R
       Left Join @Food F
         On R.FoodId = F.FoodId
		Inner Join @FoodColor FC
			On F.FoodId = FC.FoodID

The results are:
[tt][blue]

RecipeId FoodId FoodId Description FoodId Color
-------- ------ ------ -------------- ------ --------
1 100 100 Bread 100 White
[/blue][/tt]

Notice that we now only have 1 row. Why? Well, the food id of the 2 missing rows was 101 and NULL. Notice that the FoodColor table does not have a row where FoodId = 101, so that row was eliminated. As for the other row, the NULL, NULLS don't match with anything so that row is also eliminated.

Now, let's look at the same query but changing the inner join to a left join:

Set NOCOUNT ON
Declare @Recipe Table(RecipeId Int, FoodId Int)

Insert Into @Recipe Values(1, 100)
Insert Into @Recipe Values(1, 101)
Insert Into @Recipe Values(1, 102)

Declare @Food Table(FoodId Int, Description VarChar(20))
Insert Into @Food Values(100, 'Bread')
Insert Into @Food Values(101, 'Peanut Butter')

Declare @FoodColor Table(FoodId Int, Color VarChar(20))
Insert Into @FoodColor Values(100, 'White')
Insert Into @FoodColor Values(102, 'Red')

Select *
From   @Recipe R
       Left Join @Food F
         On R.FoodId = F.FoodId
		[!]Left[/!] Join @FoodColor FC
			On F.FoodId = FC.FoodID

This time the results are:
[tt][blue]

RecipeId FoodId FoodId Description FoodId Color
-------- ------ ------ ----------- -------- ---------
1 100 100 Bread 100 White
1 101 101 Peanut Butter NULL NULL
1 102 NULL NULL NULL NULL
[/blue][/tt]

I hope this contrived example helps you to understand why inner joining to a table that was already left joined to can cause problems. Any questions, just ask.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Imakeoil]

Wow George, Thank you so much. This is an excelent explanation of how these joins work. I did not realize that two joins in the same select statement could affect one another.

Your code did not work, it seemed not to read my function, however your explaination was so good that I could fix the code myself. I pulled the joins into different select statments so that they would not interfer with one another. Here is my code:


select hist_loads.shiftindex, truck as eqmt, sum (loadtons) as loadtons, sum (tk_tmcat01/3600)as oper, [name]
into #t_loadtr
from hist_loads
inner join enum_load_grp_type on hist_loads.[load] = enum_load_grp_type.num
WHERE hist_loads.shiftindex between @startd and @endd and Pdb.dbo.DefineEqmt(truck) = 'Ours'
and hist_loads.extraload = 0
Group by hist_loads.shiftindex, truck, [name]
order by truck


select hist_loads.shiftindex, excav as eqmt, sum (loadtons) as loadtons, sum (ex_tmcat01/3600)as oper, [name]
into #t_loadex
from hist_loads
inner join enum_load_grp_type on hist_loads.[load] = enum_load_grp_type.num
WHERE hist_loads.shiftindex between @startd and @endd and Pdb.dbo.DefineEqmt(excav) = 'Ours'
and hist_loads.extraload = 0
Group by hist_loads.shiftindex, excav, [name]
order by excav

select * into #t_load
from (
select *
From #t_loadtr
union all
select *
from #t_loadex
) as t


select #t_load.shiftindex, eqmtid, loadtons, oper, [name]
from hist_eqmtlist
left outer join #t_load on hist_eqmtlist.shiftindex = #t_load.shiftindex
and hist_eqmtlist.eqmtid = #t_load.eqmt

where Pdb.dbo.DefineEqmt(eqmtid) = 'Ours' and hist_eqmtlist.shiftindex between @startd and @endd



Thank you for the explanation and the example. I have been searching Google for hours and have not found anything that was even remotely as helpfull.

 

[gmmastros]

Glad I could help. If not by code, then by explanation.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

There is a blog on this topic that may be of some help as well
Why LEFT JOIN does not bring all records from the LEFT table

PluralSight Learning Library

 

[danvlas]

I think a good lesson is much more worth than just solving one little problem.

One is from me.



Daniel Vlas
Systems Consultant