SQLTeam.com | Weblogs | Forums

Joining problem - only uses 1 part of the join


#1

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,

                       c.Category, 

                       sp.Name,

                       sp.Id SalesProdID,

                       sp.TASKUCode, 

                       sp.EISKUCode,

                       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

   INNER JOIN (

          

   

          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                                                                          

                              UNION                                   

                              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.


#2

Change OR to AND


#3

that brings back 0 results unfortunately.


#4

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