Malcolm wrote:

Hi All,
I have been trying to use the left outer join but without success and I can't see what I am doing wrong, your help would be appreciated. I have
been "playing" with 2 tables (trying to do something simple to start with) wanting to list all records from one with additional data added for some from the
second.
One table contains execs and shop numbers, the other shop numbers, ECR types and phone Numbers.

I am expecting a result containing 55 rows 28 of which hold the additional data, but as you can see I only receive the 28 rows.
The tables contain identical information regarding shop numbers i.e. 1 to 55.

The plan result is:-
================================================================================
SQL statement (Executed with 4.26 Build 3)
================================================================================

select d1.exec, d.Name, d.ECRType, d.POSTelephone
from JJWLocation d1 left outer join Location d on d.locationno = d1.locationno
where d.ECRType =3

Tables Involved
---------------

JJWLocation (d1) table opened shared, has 55 rows
Location (d) table opened shared, has 55 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

WHERE Clause Execution
----------------------

Join Ordering
-------------

The driver table is the JJWLocation table (d1)

The JJWLocation table (d1) is joined to the Location table (d) with the LEFT
OUTER JOIN expression:

d1.locationno = d.locationno

Optimizer will attempt to re-order the joins to a more optimal order
Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the
optimizer to leave the joins in their declared order

The joins are already in optimal order and cannot be optimized any further

The expression:

d.ECRType = 3

will be applied to each candidate row in the result set as the result set is
generated due to the Location table (d) being the target of an OUTER join

Join Execution
--------------

Costs ARE NOT being taken into account when executing this join
Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the
optimizer to consider costs when optimizing this join

The expression:

d1.locationno = d.locationno

is OPTIMIZED

================================================================================
>>>>> 28 rows affected in 0.031 seconds
================================================================================

 
Just an FYI, another options is to move the filter to the join itself:

select d1.exec, d.Name, d.ECRType, d.POSTelephone
from JJWLocation d1
left outer join Location d
on d.locationno = d1.locationno
and d.ECRType = 3

Brandon,

<< I am expecting a result containing 55 rows 28 of which hold the
additional data, but as you can see I only receive the 28 rows. >>

Use this instead:

select d1.exec, d.Name, d.ECRType, d.POSTelephone
from JJWLocation d1 left outer join Location d on d.locationno =
d1.locationno
where d.ECRType =3 OR d.ECRType IS NULL

The LOJ will produce NULL values for some of the rows, so you need to make
sure that the first condition does not cause those rows to be removed (NULL
is never equal to anything).

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:D18CF5BD-0AFD-44D0-A78E-D3885A9C0207@news.elevatesoft.com...
> Brandon,
>
> << I am expecting a result containing 55 rows 28 of which hold the
> additional data, but as you can see I only receive the 28 rows. >>
>
> Use this instead:
>
> select d1.exec, d.Name, d.ECRType, d.POSTelephone
> from JJWLocation d1 left outer join Location d on d.locationno =
> d1.locationno
> where d.ECRType =3 OR d.ECRType IS NULL
>
Careful, this is not the same as Brandon's solution (which I think is what
OP wanted)

left outer join Location d
on d.locationno = d1.locationno
and d.ECRType = 3

Brandon's SQL will only join the ones with a value of 3 on the d table,
still extract all rows for the d1 table. Tim's solution joins all rows from
d table, then eliminates the ones that are not 3 or null. Not the same.

Robert

> The LOJ will produce NULL values for some of the rows, so you need to make
> sure that the first condition does not cause those rows to be removed
> (NULL is never equal to anything).
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Robert,

<< Careful, this is not the same as Brandon's solution (which I think is
what OP wanted) >>

Never mind - I thought that Brandon was asking the question and didn't
notice that he was posting a solution instead.

Please guys/gals, let's keep the quoting to a minimum.  Do not repost the
entire message that you're responding to - it's confusing and it takes up
way too much space.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>Please guys/gals, let's keep the quoting to a minimum. Do not repost the
>entire message that you're responding to - it's confusing and it takes up
>way too much space.

You think your ng's are bad. You should take a look at the CodeGear ones.

Roy Lambert

My apologies.  I had not yet before used the forum.  I did not realize it would quote the entire thing until after I clicked "Post."  If it is of any comfort, my own post confused me just as much

Brandon,

<< My apologies.  I had not yet before used the forum.  I did not realize it
would quote the entire thing until after I clicked "Post."  If it is of any
comfort, my own post confused me just as much >>

No problem.  One time is understandable, but I might get upset after the
10th time....

--
Tim Young
Elevate Software
www.elevatesoft.com