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
================================================================================

 

"Malcolm" <Malcolm@jjwilsonltd.com> wrote in message
news:9FA99AD3-043F-4957-A553-BA4F91AF8805@news.elevatesoft.com...
>
> 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
>

d.ECRType is null for the rows that have no match on table d1.

Robert

Hi Robert,

Not quite with you.
In table 2 the ECRType is either 1 or 12 .

What I want to achive is a list of all locations (1 to 55) with each location showing the exec and name and for those locations that have an ECRType of 3 to
show the ECRType and the telephone number.

example

Exec       Name          EcrType       Phone
AAA        FRED           3                 1234567890
BBB        BERT           NULL            NULL
CCC       JO               NULL            NULL
DDD       JOE             3                  0987654321

Regards
Malcolm

"Malcolm" <Malcolm@jjwilsonltd.com> wrote in message
news:B4E0B297-0F5D-465C-9ABB-6737AA5860FD@news.elevatesoft.com...
> Hi Robert,
>
> Not quite with you.
> In table 2 the ECRType is either 1 or 12 .
>
> What I want to achive is a list of all locations (1 to 55) with each
> location showing the exec and name and for those locations that have an
> ECRType of 3 to
> show the ECRType and the telephone number.
>
> example
>
> Exec       Name          EcrType       Phone
> AAA        FRED           3                 1234567890
> BBB        BERT           NULL            NULL
> CCC       JO               NULL            NULL
> DDD       JOE             3                  0987654321
>

Then you need to test for (EcrType = 3) or (EcrType = NULL) in the WHERE
clause. Your WHERE clause negates the left join, since it will only select
EcrType = 3 which by definition means they are rows that have a match on the
two tables.

Robert

> Regards
> Malcolm
>

Malcolm

> What I want to achive is a list of all locations (1 to 55) with each
location showing the exec and name and for those locations that have an
ECRType of 3 to
> show the ECRType and the telephone number.
>
> example
>
> Exec       Name          EcrType       Phone
> AAA        FRED           3                 1234567890
> BBB        BERT           NULL            NULL
> CCC       JO               NULL            NULL
> DDD       JOE             3                  0987654321

If you want to include all records but hide the ecrtype and phone for those
records where the ecrtype is not 3 then something like the following should
work

select d1.exec, d.Name, if(d.ECRType = 3 then d.ecrtype else
null),if(d.ecrtype=3 then d.POSTelephone else null)
from JJWLocation d1 left outer join Location d on d.locationno =
d1.locationno

John

Hi Robert
As far as I am aware, please correct me if i am wrong, the point of a left outer join was to include all records from the left table and the detail from the
right table where the claus conditions are met.

If you look at the Plan I do have a where ECRType =3, which is why in my example I show the ECRType and phone number for thos records that have an
ECRType of 3 and null in those fields for those that do not. I expect the sql to output null values because the condition is not met.

Regards
Malcolm

Hi Again

Sorry in my response with the example I should have said all records in the second table have an ECRType of either 3 or 12.

Regards
Malcolm

"Malcolm" <Malcolm@jjwilsonltd.com> wrote in message
news:A3A512C7-274A-47A1-BD5B-0EB589448E0B@news.elevatesoft.com...
> Hi Robert
> As far as I am aware, please correct me if i am wrong, the point of a left
> outer join was to include all records from the left table and the detail
> from the
> right table where the claus conditions are met.
>

Wrong. The where clause filter will be applied to all selected rows, after
the join is applied.

Robert

> If you look at the Plan I do have a where ECRType =3, which is why in my
> example I show the ECRType and phone number for thos records that have an
> ECRType of 3 and null in those fields for those that do not. I expect the
> sql to output null values because the condition is not met.
>
> Regards
> Malcolm
>

Malcolm,

<< As far as I am aware, please correct me if i am wrong, the point of a
left outer join was to include all records from the left table and the
detail from the right table where the claus conditions are met. >>

SQL is 100% left-to-right, top-to-bottom, which means that the execution
order is thus for filtering/selection:

JOIN(s)
WHERE
HAVING
TOP

In your case, the LOJs are generating NULL values for the ECRType field in
the result set, and then the WHERE clause is promptly removing the records
with these ECRType NULL values.

Robert's revised WHERE clause will do what you want.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim / Robert

Thats someting else I've learnt about SQL.

Many thanks for your help, I will go away and "play" some more

Regards
Malcolm