CODEWITHSUNDEEP
sqlsql-servert-sql
eternityhq
eternityhq

Reputation: 115

SQL with Left Join showing values that are zero

I currently have a SQL query setup but I want it to ignore 0's in the min_on_hand column, and I can't seem to figure out why this doesn't work

SELECT  
    sku_master.sku, 
    sku_master.description, 
    sku_master.min_on_hand,
    sku_master.max_on_hand, 
    x.total_qty_on_hand 
FROM 
    [FCI].dbo.[sku_master] 
LEFT JOIN
    (SELECT 
         sku_master.sku, 
         sum(location_inventory.qty_on_hand) as total_qty_on_hand 
     FROM 
         [FCI].[dbo].[location_inventory] 
     JOIN 
         [FCI].dbo.[sku_master] ON location_inventory.sku = sku_master.sku
     WHERE 
         sku_master.min_on_hand > 0 
     GROUP BY 
         sku_master.sku) x ON sku_master.sku = x.sku;

Upvotes: 0

Views: 120

Answers (4)

Rick Monteiro
Rick Monteiro

Reputation: 13

I used this query. It returns an inventory summary, for all records where the minimum on hand quantity needs to exceed 0.

`/* Left join sku_master */

    SELECT        sku_master.sku, sku_master.description, sku_master.min_on_hand, 
                  sku_master.max_on_hand, 
                  location_inventory.qty_on_hand AS total_qty_on_hand
    FROM          sku_master LEFT OUTER JOIN location_inventory 
                    ON sku_master.sku = location_inventory.sku
    GROUP BY sku_master.sku, sku_master.description, 
                  sku_master.min_on_hand, sku_master.max_on_hand, 
                  location_inventory.qty_on_hand
    HAVING        (sku_master.min_on_hand > 0)`

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

You have a correct answer but why the join in the derived table 

SELECT  
    sku_master.sku, 
    sku_master.description, 
    sku_master.min_on_hand,
    sku_master.max_on_hand, 
    x.total_qty_on_hand 
 FROM [FCI].dbo.[sku_master] 
 LEFT JOIN ( SELECT sku, 
                    sum(qty_on_hand) as total_qty_on_hand 
               FROM [FCI].[dbo].[location_inventory] 
              GROUP BY sku ) x 
   ON sku_master.sku = x.sku
WHERE sku_master.min_on_hand > 0

Upvotes: 0

eternityhq
eternityhq

Reputation: 115

SELECT  
    sku_master.sku, 
    sku_master.description, 
    sku_master.min_on_hand,
    sku_master.max_on_hand, 
    x.total_qty_on_hand 
FROM 
    [FCI].dbo.[sku_master] 
LEFT JOIN
    (SELECT 
         sku_master.sku, 
         sum(location_inventory.qty_on_hand) as total_qty_on_hand 
     FROM 
         [FCI].[dbo].[location_inventory] 
     JOIN 
         [FCI].dbo.[sku_master] ON location_inventory.sku = sku_master.sku
     GROUP BY 
         sku_master.sku) x ON sku_master.sku = x.sku;
     WHERE 
         sku_master.min_on_hand > 0

Moving the WHERE to the end of the statement fixed the issue.

Upvotes: 0

Danny
Danny

Reputation: 1759

As others have mentioned in the comments, filtering on min_on_hand in the subquery has no effect - you'll still be returned the values in sku_master, but they just won't include any of the data from x.

If you move the check to the main query then you will not see any records where min_on_hand = 0

SELECT  
    sku_master.sku, 
    sku_master.description, 
    sku_master.min_on_hand,
    sku_master.max_on_hand, 
    x.total_qty_on_hand 
FROM 
    [FCI].dbo.[sku_master] 
LEFT JOIN
    (SELECT 
         sku_master.sku, 
         sum(location_inventory.qty_on_hand) as total_qty_on_hand 
     FROM 
         [FCI].[dbo].[location_inventory] 
     JOIN 
         [FCI].dbo.[sku_master] ON location_inventory.sku = sku_master.sku
     GROUP BY 
         sku_master.sku) x ON sku_master.sku = x.sku
WHERE 
    sku_master.min_on_hand > 0

Upvotes: 1

Related Questions