Can't get all order items & keep getting orders lines which have sub-components

Question

Hi I hope someone can help me or give me a clue on a SQL query I need to develop a Pick List report in Crystal Report 2008. The following query on displays order lines of items which have sub-components of a parent part and NOT order lines of items which are not sub-components and just stand alone items. We need all order lines to be displayed. Can someone please assist with this?

SELECT "SorMaster"."SalesOrder",
       "SorMaster"."Customer",
       "SorMaster"."OrderDate",
       "CusSorMaster_"."CheckedBy",
       "ArCustomer"."SoldToAddr1",
       "ArCustomer"."SoldToAddr2",
       "ArCustomer"."SoldToAddr3",
       "ArCustomer"."SoldToAddr4",
       "ArCustomer"."SoldToAddr5",
       "ArCustomer"."SoldPostalCode",
       "SorMaster"."ShipAddress1",
       "SorMaster"."ShipAddress2",
       "SorMaster"."ShipAddress3",
       "SorMaster"."ShipAddress4",
       "SorMaster"."ShipAddress5",
       "SorMaster"."ShipPostalCode",
       "ArCustomer"."Name",
       "SorMaster"."CustomerName",
       "SorMaster"."CustomerPoNumber",
       "CusSorMaster_"."CusJobRef",
       "SorDetail"."MOrderQty",
       "SorDetail"."MStockDes",
       "SorDetail"."MBackOrderQty",
       "SorDetail"."MShipQty",
       "SorMaster"."ReqShipDate",
       "SorDetail"."LineType",
       "SorMaster"."ShippingInstrs",
       "SorMaster"."Salesperson",
       "SorDetail"."MStockCode",
       "InvWarehouse"."DefaultBin",
       "BomNarration"."Narration",
       "BomStructure"."Component",
       "InvMaster"."Description",
       "BomStructure"."QtyPer"
FROM("SysproCompanyT"."dbo"."InvMaster" "InvMaster"
     INNER JOIN((((("SysproCompanyT"."dbo"."SorDetail" "SorDetail"
                    INNER JOIN "SysproCompanyT"."dbo"."InvWarehouse" "InvWarehouse" ON ("SorDetail"."MStockCode" = "InvWarehouse"."StockCode")
                                                                                   AND ("SorDetail"."MWarehouse" = "InvWarehouse"."Warehouse"))
                   INNER JOIN "SysproCompanyT"."dbo"."SorMaster" "SorMaster" ON "SorDetail"."SalesOrder" = "SorMaster"."SalesOrder")
                  INNER JOIN "SysproCompanyT"."dbo"."BomStructure" "BomStructure" ON "SorDetail"."MStockCode" = "BomStructure"."ParentPart")
                 INNER JOIN "SysproCompanyT"."dbo"."CusSorMaster+" "CusSorMaster_" ON "SorMaster"."SalesOrder" = "CusSorMaster_"."SalesOrder")
                FULL OUTER JOIN "SysproCompanyT"."dbo"."ArCustomer" "ArCustomer" ON "SorMaster"."Customer" = "ArCustomer"."Customer")ON "InvMaster"."StockCode" = "BomStructure"."Component")
    LEFT OUTER JOIN "SysproCompanyT"."dbo"."BomNarration" "BomNarration" ON "BomStructure"."AutoNarrCode" = "BomNarration"."NarrationNum"
WHERE ("SorDetail"."LineType" = '1'
    OR "SorDetail"."LineType" = '7')
ORDER BY "SorMaster"."SalesOrder",
         "SorDetail"."MStockCode",
         "InvWarehouse"."DefaultBin";

Answer 1

Your problem is the JOIN on BomStructure as you want the items that are not in this table. You can try stg like this but I am a bit scared about the performances :

SELECT "SorMaster"."SalesOrder",
--- ... No change between these lines ...
       "InvWarehouse"."DefaultBin",
       tmpBom."Narration",
       tmpBom."Component",
       tmpBom."Description",
       tmpBom."QtyPer"
FROM    
        (           
            (

                "SysproCompanyT"."dbo"."SorDetail" "SorDetail"
                INNER JOIN "SysproCompanyT"."dbo"."InvWarehouse" "InvWarehouse" ON ("SorDetail"."MStockCode" = "InvWarehouse"."StockCode") AND ("SorDetail"."MWarehouse" = "InvWarehouse"."Warehouse")
                INNER JOIN "SysproCompanyT"."dbo"."SorMaster" "SorMaster" ON "SorDetail"."SalesOrder" = "SorMaster"."SalesOrder"
                INNER JOIN "SysproCompanyT"."dbo"."CusSorMaster+" "CusSorMaster_" ON "SorMaster"."SalesOrder" = "CusSorMaster_"."SalesOrder"
                INNER JOIN (
                    SELECT  InvMaster.Code AS Component, InvMaster.Description, BomStructure.QtyPer, BomNarration.Narration, ISNULL(BomStructure.ParentPart, InvMaster.StockCode) AS ParentOrOrphan
                    FROM    "SysproCompanyT"."dbo"."InvMaster" "InvMaster" 
                            LEFT JOIN "SysproCompanyT"."dbo"."BomStructure" ON "BomStructure"."Component" = "InvMaster"."StockCode"
                            LEFT JOIN "SysproCompanyT"."dbo"."BomNarration" "BomNarration" ON "BomStructure"."AutoNarrCode" = "BomNarration"."NarrationNum"
                    ) tmpBom ON "SorDetail"."MStockCode" = tmpBom.ParentOrOrphan
            )
        )
        FULL OUTER JOIN "SysproCompanyT"."dbo"."ArCustomer" "ArCustomer" ON "SorMaster"."Customer" = "ArCustomer"."Customer"
WHERE ("SorDetail"."LineType" = '1' OR "SorDetail"."LineType" = '7')
-- TO BE REPLACED BY : WHERE ("SorDetail"."LineType" IN ('1', '7')
ORDER BY "SorMaster"."SalesOrder",
         "SorDetail"."MStockCode",
         "InvWarehouse"."DefaultBin";