1

Actually I got my output, But it partial.

In my left table I have all the records, But when I do filter in right table it is not bringing all the records from left table

Liam neesan
  • 2,282
  • 6
  • 33
  • 72
  • @Realcheeselord can you see, it brings only matched records from right table. It doesn't show unmatched records also from left table. I want to bring unmatched record also – Liam neesan Aug 03 '17 at 12:38
  • 1
    @RealCheeseLord In this case you don't need a question , because you have somthing called _Expecting output_ – Ilyes Aug 03 '17 at 12:38
  • Possible duplicate of [LEFT OUTER JOIN query not returning expected rows](https://stackoverflow.com/questions/14861927/left-outer-join-query-not-returning-expected-rows) – Tab Alleman Aug 03 '17 at 12:42
  • yeah, sorry. didn't see it the first time – RealCheeseLord Aug 03 '17 at 12:42
  • @mohamedfaiz So what if user will do filter at any different date? You still can (and have to) do that filter in `join` – Nenad Zivkovic Aug 03 '17 at 12:56
  • @mohamedfailz What do you mean by that a user will filter the data at any different date? The user will edit the query by himself and execute it, or the user will provide start and end dates via an UI to filter the data? The SQL query is part of a stored procedure, or is invoked from code (e.g. ADO.NET), etc.? – Mislav Zic Aug 03 '17 at 13:53
  • @Mislavzic yes you are correct, user will do filter from UI. That UI will add the filter in Where clause only. Not in join. That is the problem. – Liam neesan Aug 04 '17 at 13:12

5 Answers5

2

Table you use on the right side of LEFT JOIN you shouldn't put in WHERE clause because that effectively turns LEFT JOIN into INNER.

Move your conditions to the JOIN clause itself:

LEFT JOIN Item ON dynamics.ItemID = Item.ID  
  AND item.ItemLookupCode = '100006C0005' 
  AND Item.ParentItem = 0 
  AND Item.ItemType <> 9
LEFT JOIN Sales idsp ON idsp.ItemID = Item.ID 
  AND idsp.StoreID = dynamics.StoreID 
  AND idsp.Time between '2017-07-16' and '2017-07-31' 
  AND idsp.StoreID <> 1001 
Nenad Zivkovic
  • 18,221
  • 6
  • 42
  • 55
1

obviusly it doesen't show left data because the WHERE clausule exclude them

you can add an OR idsp.Time == 0 to the WHERE

Where
item.ItemLookupCode = '100006C0005' and
(idsp.Time between '2017-07-16' and '2017-07-31' and idsp.StoreID <> 1001 and Item.ParentItem = 0 and Item.ItemType <> 9) OR idsp.Time IS NULL
Iaconis Simone
  • 282
  • 3
  • 12
1

Add the WHERE condition to your join:

LEFT JOIN Sales idsp WITH(NOLOCK) 
       ON idsp.ItemID = Item.ID 
      AND Item.ParentItem = 0 
      AND idsp.StoreID = dynamics.StoreID
      AND idsp.Time between '2017-07-16' and '2017-07-31' 
      AND idsp.StoreID <> 1001

And remove it from your WHERE

RealCheeseLord
  • 785
  • 1
  • 12
  • 24
0

I think left join to store is what you are looking for:

from 
    ItemDynamic dynamics
    inner join Store WITH(NOLOCK) on dynamics.StoreID = Store.ID and store.Inactive = 0
    LEFT JOIN Item WITH(NOLOCK) on dynamics.ItemID = Item.ID and Item.ParentItem = 0
    LEFT JOIN Sales idsp WITH(NOLOCK) on idsp.ItemID = Item.ID and Item.ParentItem = 0 and idsp.StoreID = dynamics.StoreID
    and idsp.Time between '2017-07-16' and '2017-07-31' and idsp.StoreID <> 1001 and Item.ParentItem = 0 and Item.ItemType <> 9
    LEFT JOIN Department WITH(NOLOCK) on Department.ID = Item.DepartmentID
    LEFT JOIN Category WITH(NOLOCK) on Category.ID = item.CategoryID
    LEFT JOIN Supplier WITH(NOLOCK) on Supplier.ID = item.SupplierID
Where
    item.ItemLookupCode = '100006C0005' and
 --   idsp.Time between '2017-07-16' and '2017-07-31' and idsp.StoreID <> 1001 and Item.ParentItem = 0 and Item.ItemType <> 9

You might require to add the condition in left join and comment in where clause

Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38
0
try with following code

remove your where clause and put those conditions with your joins




select 
    dynamics.ItemID,
    item.ItemLookupCode,
    dynamics.StoreID,
    Department.Name Department,
    Category.Name Category,
    Supplier.Code,
    Supplier.SupplierName,
    sum(idsp.Qty) SoldQty,
    sum(idsp.ExtendedCost) SoldExtCost,
    sum(idsp.ExtendedPrice) SoldExtPrice,
    dynamics.RestockLevel,
    CASE WHEN isNull(sum(idsp.Qty),0) > (dynamics.RestockLevel * 0.75) THEN 'Fast Moving'
    WHEN isNull(sum(idsp.Qty),0) > (dynamics.RestockLevel * 0.25) THEN 'Average Moving'
    WHEN isNull(sum(idsp.Qty),0) > 0 THEN 'Slow Moving'
    WHEN isNull(sum(idsp.Qty),0) = 0 THEN 'No Moving' END AS Moving
from 
    ItemDynamic dynamics
    inner join Store WITH(NOLOCK) on dynamics.StoreID = Store.ID and store.Inactive = 0
    LEFT JOIN Item WITH(NOLOCK) on dynamics.ItemID = Item.ID and Item.ParentItem = 0 and item.ItemLookupCode = '100006C0005'
    LEFT JOIN Sales idsp WITH(NOLOCK) on idsp.ItemID = Item.ID and Item.ParentItem = 0 and idsp.StoreID = dynamics.StoreID and idsp.Time between '2017-07-16' and '2017-07-31' and idsp.StoreID <> 1001 and Item.ParentItem = 0 and Item.ItemType <> 9
    LEFT JOIN Department WITH(NOLOCK) on Department.ID = Item.DepartmentID
    LEFT JOIN Category WITH(NOLOCK) on Category.ID = item.CategoryID
    LEFT JOIN Supplier WITH(NOLOCK) on Supplier.ID = item.SupplierID


Group By 
    dynamics.ItemID,
    item.ItemLookupCode,
    dynamics.StoreID,
    dynamics.RestockLevel,
    Department.Name,
    Category.Name,
    Supplier.Code,
    Supplier.SupplierName
order by 
    item.ItemLookupCode