Hi All

I am trying to use a left outer join but am not getting the correct results.
Here it is

SELECT glpd_no
FROM   glperiod LEFT OUTER JOIN  invnmove ON
(glpd_no=nvtmper_glpdno) AND (glpd_company=nvtm_company)
where  (glpd_no>='200501' AND glpd_no<='200505') AND
nvtm_type='in' AND
nvtmsku_skucd='battery' AND
glpd_division='tape'

I get the following results
200504
200504

But if i try the "old syntax" as follows


SELECT glperiod.glpd_no
FROM   glperiod , invnmove
where  (glpd_no *= nvtmper_glpdno) AND (glpd_company *= nvtm_company)
and  (glpd_no>='200501' AND glpd_no<='200505')
AND nvtm_type='in' AND nvtmsku_skucd='battery'
AND glpd_division='tape'

i get the following results which is what i want

200501
200502
200503
200504
200504
200505


I am running SQL server 2000 service pack 4

Any help would be greatly appreciated

thank you in advance

can post ddl/sample data
Regards
R.D

Show quoteHide quote

Hi

Using

CREATE TABLE glperiod ( glpd_no char(6), glpd_company char(10),
glpd_division char(4) )


INSERT INTO glperiod ( glpd_no, glpd_company, glpd_division  )
SELECT '200501' , 'Zcorps','tape'
UNION ALL SELECT '200502' , 'Zcorps','tape'
UNION ALL SELECT '200503' , 'Zcorps','tape'
UNION ALL SELECT '200504' , 'Dcorps','tape'
UNION ALL SELECT '200504' , 'Zcorps','tape'
UNION ALL SELECT '200505' , 'Zcorps','tape'


CREATE TABLE invnmove ( nvtmper_glpdno char(6),nvtm_company
char(10),nvtm_type char(2),nvtmsku_skucd char(7) )

INSERT INTO invnmove ( nvtmper_glpdno,nvtm_company,nvtm_type,
nvtmsku_skucd )
SELECT '200501' , 'Acorps','in','battery'
UNION ALL SELECT '200502' , 'Bcorps','in','battery'
UNION ALL SELECT '200503' , 'Ccorps','in','battery'
UNION ALL SELECT '200504' , 'Dcorps','in','battery'
UNION ALL SELECT '200504' , 'Zcorps','in','battery'
UNION ALL SELECT '200505' , 'Ecorps','in','battery'


Try:

SELECT g.glpd_no
FROM   glperiod g
LEFT JOIN  invnmove i ON g.glpd_no=i.nvtmper_glpdno
AND    g.glpd_company=i.nvtm_company
AND    g.glpd_no>='200501'
AND    g.glpd_no<='200505'
AND    i.nvtm_type='in'
AND    i.nvtmsku_skucd='battery'
AND    g.glpd_division='tape'

John



Show quoteHide quote

Here is how OUTER JOINs work in SQL-92.  Assume you are given:

Table1       Table2
a   b        a   c
======       ======
1   w        1   r
2   x        2   s
3   y        3   t
4   z

and the outer join expression:

Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a      <== join condition
    AND Table2.c = 't';      <== single table condition

We call Table1 the "preserved table" and Table2 the "unpreserved table"
in the query.  What I am going to give you is a little different, but
equivalent to the ANSI/ISO standards.

1) We build the CROSS JOIN of the two tables.  Scan each row in the
result set.

2) If the predicate tests TRUE for that row, then you keep it. You also
remove all rows derived from it from the CROSS JOIN

3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.

So let us execute this by hand:

Let @ = passed the first predicate
Let * = passed the second predicate

Table1 CROSS JOIN Table2
a   b        a   c
=========================
1   w       1   r @
1   w       2   s
1   w       3   t *
2   x       1   r
2   x       2   s @
2   x       3   t *
3   y       1   r
3   y       2   s
3   y       3   t @* <== the TRUE set
4   z       1   r
4   z       2   s
4   z       3   t *

Table1 LEFT OUTER JOIN Table2
a   b        a   c
=========================
3   y     3      t      <= only TRUE row
-----------------------
1   w     NULL   NULL   Sets of duplicates
1   w     NULL   NULL
1   w     NULL   NULL
-----------------------
2   x     NULL   NULL
2   x     NULL   NULL
2   x     NULL   NULL
3   y     NULL   NULL  <== derived from the TRUE set - Remove
3   y     NULL   NULL
-----------------------
4   z     NULL   NULL
4   z     NULL   NULL
4   z     NULL   NULL

the final results:

Table1 LEFT OUTER JOIN Table2
a   b        a   c
=========================
1   w     NULL   NULL
2   x     NULL   NULL
3   y     3      t
4   z     NULL   NULL

The basic rule is that every row in the preserved table is represented
in the results in at least one result row.

There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products.  Consider the two Chris Date tables

Suppliers        SupParts
supno             supno partno qty
=========        ==============
S1               S1   P1    100
S2               S1   P2    250
S3               S2   P1    100
                  S2   P2    250

and let's do an extended equality outer join like this:

SELECT *
  FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
   AND qty < 200;

If I do the outer first, I get:

Suppliers LOJ SupParts
supno supno partno qty
=======================
S1     S1   P1    100
S1     S1   P2    250
S2     S2   P1    100
S2     S2   P2    250
S3   NULL  NULL   NULL

Then I apply the (qty < 200) predicate and get

Suppliers LOJ SupParts
supno supno partno qty
===================
S1   S1   P1    100
S2   S2   P1    100

Doing it in the opposite order

Suppliers LOJ SupParts
supno supno partno qty
===================
S1   S1   P1    100
S2   S2   P1    100
S3   NULL NULL  NULL

Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds!  In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...

SELECT *
   FROM Supplier
        LEFT OUTER JOIN
        SupParts
        ON Supplier.supno = SupParts.supno
WHERE qty < 200;

... or do it in the joining:

SELECT *
  FROM Supplier
       LEFT OUTER JOIN
       SupParts
       ON Supplier.supno = SupParts.supno
          AND qty < 200;

Another problem is that you cannot show the same table as preserved and
unpreserved in the extended equality version, but it is easy in SQL-92.
For example to find the students who have taken Math 101 and might
have taken Math 102:

SELECT C1.student, C1.math, C2.math
  FROM (SELECT * FROM Courses WHERE math = 101) AS C1
       LEFT OUTER JOIN
       (SELECT * FROM Courses WHERE math = 102) AS C2
       ON C1.student = C2.student;

Update Lock
select variable in from statement
Date/String format
PRIMARY KEY
Another case for IDENTITY