SQLTeam.com | Weblogs | Forums

Joining problem - only uses 1 part of the join


I am having issue with a query I am trying to fix, which was not developed by me.
Basically what happens is that the join statement only seems to join to 1 part of the condition but not the other. Perhaps the query needs to be modified. In any event, I need to bring back both conditions in the query.

SELECT * INTO #tempHistory

   FROM   dbo.Sales_History 
   WHERE  DATEDIFF(D,DateOfTransaction,@DateRequired) <= 14
   AND           DATEDIFF(D,DateOfTransaction,@DateRequired) >= 1
   AND           StoreID = @StoreID                     

   --the stored procedure always expects MondaySales as the first day of the week to be passed in as date required

   --First Load all the data from the database into a temp table

   SELECT     @StoreID StoreID, 

                       @DateRequired DateRequired,

                       c.CategoryID CategoryID,



                       sp.Id SalesProdID,



                       ISNULL(sp.IncludeInBuildDef,0) IncludeInBuildDef,

                       ISNULL(sp.IncludeInBuildExport,0) IncludeInBuildExport,

                       0 MondaySales,

                       0 MondayOC,

                       0 MondayMods,

                       0 TuesdaySales,

                       0 TuesdayOC,

                       0 TuesdayMods,

                       0 WednesdaySales,

                       0 WednesdayOC,

                       0 WednesdayMods,

                       0 ThursdaySales,

                       0 ThursdayOC,

                       0 ThursdayMods,

                       0 FridaySales,

                       0 FridayOC,

                       0 FridayMods,

                       0 SaturdaySales,

                       0 SaturdayOC,

                       0 SaturdayMods,

                       0 SundaySales,

                       0 SundayOC,

                       0 SundayMods,

                       0 TotalSales,

                       0 TotalOC,

                       0 TotalMods 

   --INTO        #tempBuild_SalesProductData 

   FROM          dbo.Prod_Menu m 

   INNER JOIN    dbo.Prod_MenuProducts mp ON m.Id = mp.MenuID

   INNER JOIN    dbo.Prod_SalesProducts sp on mp.ProductId = sp.Id

   INNER JOIN    dbo.Prod_ProductionCategories c on c.CategoryID = COALESCE(dbo.prod_GetProductionCategory(sp.CategoryID), sp.CategoryID)

   WHERE         DATEDIFF(D, m.StartDate, GETDATE()) >= 0 

   AND                  DATEDIFF(D, m.EndDate, GETDATE()) < 0

   AND                  ISNULL(sp.Deleted,0) = 0

   ORDER BY      sp.Name

   --Then update this temp table with data for the different days of the week

   --GET MondaySales

   DECLARE @DateRequired DateTime

   SET           @DateRequired = '2015-7-06'

   DECLARE @StoreID Uniqueidentifier

   SET           @StoreID = 'E54E7A48-89F4-4FC5-A373-14FEC18F5BA9'

   --UPDATE b SET MondaySales = p.Quantity

   select *

   FROM #tempBuild_SalesProductData b




          SELECT SKUCode, ISNULL(CEILING(AVG(Quantity)),0) Quantity

          FROM (             

                              SELECT ss.SKUCode, SUM(Quantity) Quantity

                              FROM   #tempHistory ss 

                              WHERE  DATEDIFF(D, DateOfTransaction, @DateRequired) = 14

                              AND           (ss.StoreID = @StoreID)

                              AND           ISNULL(ss.StaffFood, 0) = 0

                              AND           ISNULL(ss.Waste, 0) = 0

                              GROUP BY SKUCode                                                                          


                              SELECT ss.SKUCode, SUM(Quantity) Quantity

                              FROM   #tempHistory ss

                              WHERE  DATEDIFF(D, DateOfTransaction, @DateRequired) = 7

                              AND           (ss.StoreID = @StoreID)

                              AND                  ISNULL(ss.StaffFood, 0) = 0

                              AND                  ISNULL(ss.Waste, 0) = 0

                              GROUP BY SKUCode

                 ) p

          GROUP BY SKUCode

   ) p ON (p.SKUCode = b.TASKUCode OR p.SKUCode = b.EISKUCode)

   WHERE  StoreID = @StoreID   

   AND           b.DateRequired = @DateRequired

   select * from #tempBuild_SalesProductData where name='Sweet Potato Falafel & Hummus Wrap'

so, the query here has been altered slightly for debugging purposes, providing in date/store ID.
I believe the problem is in the join below:

p ON (p.SKUCode = b.TASKUCode OR p.SKUCode = b.EISKUCode)

It seems to only pull in the TASKUCode join and not the EISKUCode

how should the query been fixed to bring in data from both parts of the join?

thank you.


Change OR to AND


that brings back 0 results unfortunately.


Well then, you have to decide what you want. If you want both conditions, use AND, if either one will do , use OR. If EISKUCODE is not being pulled in, it is because there is no row where p.SKUCode = b.EISKUCode. You can verify that by changing the ON condition (temporarily) to:

ON p.SKUCode = b.EISKUCode