Weird problem: Cannot run Left Outer Join properly

23 Nov 2005 9:36 PM

Bill Nguyen

The following statement will display all records in WF_FMDispatch table. In
this case, the LEFT OUTER JOIN is working.
However, as soon as I filter the WF_SupplierEntity table , only records with
matching pair f_vendor_no = factorSupplierID are shown (statement #2).
Am I missing something here?

Thanks

Bill

#1:

SELECT     TOP 100 PERCENT dbo.WF_FMDispatch.f_movement_no,
dbo.WF_FMDispatch.f_vendor_no, dbo.WF_FMDispatch.f_terminal_no,
                      dbo.WF_FMDispatch.f_carrier_no,
dbo.WF_FMDispatch.f_prft_no, dbo.WF_FMDispatch.f_bol_no,
dbo.WF_FMDispatch.f_tank_no,
                      dbo.WF_FMDispatch.f_product_no,
dbo.WF_FMDispatch.f_bol_date, dbo.WF_FMDispatch.f_invoice,
dbo.WF_FMDispatch.f_bolgross,
                      dbo.WF_FMDispatch.f_bolnet,
dbo.WF_FMDispatch.f_delgross, dbo.WF_FMDispatch.f_delnet,
dbo.WF_FMDispatch.f_balgross,
                      dbo.WF_FMDispatch.f_balnet,
dbo.WF_FMDispatch.f_cust_dest, dbo.WF_FMDispatch.f_driver_no,
dbo.WF_FMDispatch.f_truck_no,
                      dbo.WF_FMDispatch.f_product_class,
dbo.WF_FMDispatch.f_completion_flag, dbo.WF_SupplierEntity.entityID,
                      dbo.WF_SupplierEntity.AlternateSupplierName
FROM         dbo.WF_FMDispatch LEFT OUTER JOIN
                      dbo.WF_SupplierEntity ON dbo.WF_FMDispatch.f_vendor_no
= dbo.WF_SupplierEntity.factorSupplierID

ORDER BY dbo.WF_FMDispatch.f_movement_no

# 2:

SELECT     TOP 100 PERCENT dbo.WF_FMDispatch.f_movement_no,
dbo.WF_FMDispatch.f_vendor_no, dbo.WF_FMDispatch.f_terminal_no,
                      dbo.WF_FMDispatch.f_carrier_no,
dbo.WF_FMDispatch.f_prft_no, dbo.WF_FMDispatch.f_bol_no,
dbo.WF_FMDispatch.f_tank_no,
                      dbo.WF_FMDispatch.f_product_no,
dbo.WF_FMDispatch.f_bol_date, dbo.WF_FMDispatch.f_invoice,
dbo.WF_FMDispatch.f_bolgross,
                      dbo.WF_FMDispatch.f_bolnet,
dbo.WF_FMDispatch.f_delgross, dbo.WF_FMDispatch.f_delnet,
dbo.WF_FMDispatch.f_balgross,
                      dbo.WF_FMDispatch.f_balnet,
dbo.WF_FMDispatch.f_cust_dest, dbo.WF_FMDispatch.f_driver_no,
dbo.WF_FMDispatch.f_truck_no,
                      dbo.WF_FMDispatch.f_product_class,
dbo.WF_FMDispatch.f_completion_flag, dbo.WF_SupplierEntity.entityID,
                      dbo.WF_SupplierEntity.AlternateSupplierName

FROM         dbo.WF_FMDispatch LEFT OUTER JOIN
                      dbo.WF_SupplierEntity ON dbo.WF_FMDispatch.f_vendor_no
= dbo.WF_SupplierEntity.factorSupplierID
WHERE    dbo.WF_SupplierEntity.entityID = 2
ORDER BY dbo.WF_FMDispatch.f_movement_no

23 Nov 2005 10:02 PM

Dandy Weyn [Dandyman]

In your statement you specified the condition
dbo.WF_SupplierEntity.entityID = 2
which means the outer join will be performed anyhow, but only return the
records that have entityid =2
you might want to add an OR condition that says
dbo.WF_SupplierEntity.entityID = NULL
because the LEFT OUTER JOIN will generate NULL values for the records in the
LEFT table that don't have an entry in the right table

--
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net

Check my SQL Server Resource Pages at http://www.dandyman.net/sql

Show quote

"Bill Nguyen" <billn_nospam_please@jaco.com> wrote in message
news:%23eQZxXH8FHA.1600@TK2MSFTNGP10.phx.gbl...
> The following statement will display all records in WF_FMDispatch table.
> In this case, the LEFT OUTER JOIN is working.
> However, as soon as I filter the WF_SupplierEntity table , only records
> with matching pair f_vendor_no = factorSupplierID are shown (statement
> #2).
> Am I missing something here?
>
> Thanks
>
> Bill
>
>
>
> #1:
>
> SELECT     TOP 100 PERCENT dbo.WF_FMDispatch.f_movement_no,
> dbo.WF_FMDispatch.f_vendor_no, dbo.WF_FMDispatch.f_terminal_no,
>                      dbo.WF_FMDispatch.f_carrier_no,
> dbo.WF_FMDispatch.f_prft_no, dbo.WF_FMDispatch.f_bol_no,
> dbo.WF_FMDispatch.f_tank_no,
>                      dbo.WF_FMDispatch.f_product_no,
> dbo.WF_FMDispatch.f_bol_date, dbo.WF_FMDispatch.f_invoice,
> dbo.WF_FMDispatch.f_bolgross,
>                      dbo.WF_FMDispatch.f_bolnet,
> dbo.WF_FMDispatch.f_delgross, dbo.WF_FMDispatch.f_delnet,
> dbo.WF_FMDispatch.f_balgross,
>                      dbo.WF_FMDispatch.f_balnet,
> dbo.WF_FMDispatch.f_cust_dest, dbo.WF_FMDispatch.f_driver_no,
> dbo.WF_FMDispatch.f_truck_no,
>                      dbo.WF_FMDispatch.f_product_class,
> dbo.WF_FMDispatch.f_completion_flag, dbo.WF_SupplierEntity.entityID,
>                      dbo.WF_SupplierEntity.AlternateSupplierName
> FROM         dbo.WF_FMDispatch LEFT OUTER JOIN
>                      dbo.WF_SupplierEntity ON
> dbo.WF_FMDispatch.f_vendor_no = dbo.WF_SupplierEntity.factorSupplierID
>
> ORDER BY dbo.WF_FMDispatch.f_movement_no
>
>
> # 2:
>
> SELECT     TOP 100 PERCENT dbo.WF_FMDispatch.f_movement_no,
> dbo.WF_FMDispatch.f_vendor_no, dbo.WF_FMDispatch.f_terminal_no,
>                      dbo.WF_FMDispatch.f_carrier_no,
> dbo.WF_FMDispatch.f_prft_no, dbo.WF_FMDispatch.f_bol_no,
> dbo.WF_FMDispatch.f_tank_no,
>                      dbo.WF_FMDispatch.f_product_no,
> dbo.WF_FMDispatch.f_bol_date, dbo.WF_FMDispatch.f_invoice,
> dbo.WF_FMDispatch.f_bolgross,
>                      dbo.WF_FMDispatch.f_bolnet,
> dbo.WF_FMDispatch.f_delgross, dbo.WF_FMDispatch.f_delnet,
> dbo.WF_FMDispatch.f_balgross,
>                      dbo.WF_FMDispatch.f_balnet,
> dbo.WF_FMDispatch.f_cust_dest, dbo.WF_FMDispatch.f_driver_no,
> dbo.WF_FMDispatch.f_truck_no,
>                      dbo.WF_FMDispatch.f_product_class,
> dbo.WF_FMDispatch.f_completion_flag, dbo.WF_SupplierEntity.entityID,
>                      dbo.WF_SupplierEntity.AlternateSupplierName
>
> FROM         dbo.WF_FMDispatch LEFT OUTER JOIN
>                      dbo.WF_SupplierEntity ON
> dbo.WF_FMDispatch.f_vendor_no = dbo.WF_SupplierEntity.factorSupplierID
> WHERE    dbo.WF_SupplierEntity.entityID = 2
> ORDER BY dbo.WF_FMDispatch.f_movement_no
>

23 Nov 2005 10:24 PM

Trey Walpole

The where is applied after the join, so null values are being eliminated

Move the dbo.WF_SupplierEntity.entityID = 2 from where clause to the
join, e.g.

....
LEFT OUTER JOIN dbo.WF_SupplierEntity ON dbo.WF_FMDispatch.f_vendor_no
= dbo.WF_SupplierEntity.factorSupplierID
AND dbo.WF_SupplierEntity.entityID = 2
....

Bill Nguyen wrote:
Show quote

> The following statement will display all records in WF_FMDispatch table. In
> this case, the LEFT OUTER JOIN is working.
> However, as soon as I filter the WF_SupplierEntity table , only records with
> matching pair f_vendor_no = factorSupplierID are shown (statement #2).
> Am I missing something here?
>
> Thanks
>
> Bill
>
>
>
> #1:
>
> SELECT     TOP 100 PERCENT dbo.WF_FMDispatch.f_movement_no,
> dbo.WF_FMDispatch.f_vendor_no, dbo.WF_FMDispatch.f_terminal_no,
>                       dbo.WF_FMDispatch.f_carrier_no,
> dbo.WF_FMDispatch.f_prft_no, dbo.WF_FMDispatch.f_bol_no,
> dbo.WF_FMDispatch.f_tank_no,
>                       dbo.WF_FMDispatch.f_product_no,
> dbo.WF_FMDispatch.f_bol_date, dbo.WF_FMDispatch.f_invoice,
> dbo.WF_FMDispatch.f_bolgross,
>                       dbo.WF_FMDispatch.f_bolnet,
> dbo.WF_FMDispatch.f_delgross, dbo.WF_FMDispatch.f_delnet,
> dbo.WF_FMDispatch.f_balgross,
>                       dbo.WF_FMDispatch.f_balnet,
> dbo.WF_FMDispatch.f_cust_dest, dbo.WF_FMDispatch.f_driver_no,
> dbo.WF_FMDispatch.f_truck_no,
>                       dbo.WF_FMDispatch.f_product_class,
> dbo.WF_FMDispatch.f_completion_flag, dbo.WF_SupplierEntity.entityID,
>                       dbo.WF_SupplierEntity.AlternateSupplierName
> FROM         dbo.WF_FMDispatch LEFT OUTER JOIN
>                       dbo.WF_SupplierEntity ON dbo.WF_FMDispatch.f_vendor_no
> = dbo.WF_SupplierEntity.factorSupplierID
>
> ORDER BY dbo.WF_FMDispatch.f_movement_no
>
>
> # 2:
>
> SELECT     TOP 100 PERCENT dbo.WF_FMDispatch.f_movement_no,
> dbo.WF_FMDispatch.f_vendor_no, dbo.WF_FMDispatch.f_terminal_no,
>                       dbo.WF_FMDispatch.f_carrier_no,
> dbo.WF_FMDispatch.f_prft_no, dbo.WF_FMDispatch.f_bol_no,
> dbo.WF_FMDispatch.f_tank_no,
>                       dbo.WF_FMDispatch.f_product_no,
> dbo.WF_FMDispatch.f_bol_date, dbo.WF_FMDispatch.f_invoice,
> dbo.WF_FMDispatch.f_bolgross,
>                       dbo.WF_FMDispatch.f_bolnet,
> dbo.WF_FMDispatch.f_delgross, dbo.WF_FMDispatch.f_delnet,
> dbo.WF_FMDispatch.f_balgross,
>                       dbo.WF_FMDispatch.f_balnet,
> dbo.WF_FMDispatch.f_cust_dest, dbo.WF_FMDispatch.f_driver_no,
> dbo.WF_FMDispatch.f_truck_no,
>                       dbo.WF_FMDispatch.f_product_class,
> dbo.WF_FMDispatch.f_completion_flag, dbo.WF_SupplierEntity.entityID,
>                       dbo.WF_SupplierEntity.AlternateSupplierName
>
> FROM         dbo.WF_FMDispatch LEFT OUTER JOIN
>                       dbo.WF_SupplierEntity ON dbo.WF_FMDispatch.f_vendor_no
> = dbo.WF_SupplierEntity.factorSupplierID
> WHERE    dbo.WF_SupplierEntity.entityID = 2
> ORDER BY dbo.WF_FMDispatch.f_movement_no
>
>

Weird problem: Cannot run Left Outer Join properly - SQLserver 2000