locked
Query not returning all rows RRS feed

  • Question

  • I'm trying to set a query that will return all products that a load requires along with the amount that has been allocated to it. I have this semi working, only thing is it will only provide me with a product that has an allocated quantity already. A little help please.

    Select  P.ProductDescription as Product, ISNULL(SUM(PD.CaseQty),0) as AllocatedQty, L.Qty as RequiredQty
    	From 	PalletDetail PD 
    	Inner Join 
    	RunHeaders R on PD.RunId = R.RunId
    	Inner Join
    	Products P on P.ProductId = R.ProductId
    	Inner Join
    	LoadProduct L on L.ProductId = P.ProductId
    	Where 	PD.LoadId 	= @LoadId And PD.RunId = R.RunId And PD.LoadId = L.LoadId And R.ProductId = P.ProductId And R.ProductId = L.ProductId
    	Group By L.LoadId, P.ProductId, P.ProductDescription, L.Qty

    Wednesday, November 27, 2013 6:19 AM

Answers

  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you were pretty good). Temporal data should use ISO-8601 formats. Code should be in Standard SQL AS much AS possible and not local dialect. That means we use COALESCE(), not ISNULL() 

    This is minimal polite behavior on SQL forums. 

    I see that you so not use an industry standard like UPC, EAN, GTIN, etc for your products. You have a magical, vague “qty” in the Load_Products table; this is a violation of ISO-11179 because we have to have “<something in particular>_qty” in a valid data model. 

    Noobs who still think in sequential programming languages use the infixed INNER JOINs while experienced SQL programmers do not. Did you notice how many of your search conditions were repeated? It becomes obvious with a set-oriented syntax. 

    Why do you group by columns that are not in the SELECT? 

    Here is my guess without any specs or DDL. Does your boss treat you with this kind of contempt? 

    SELECT P.product_description, 
           L.required_qty,
          (SUM(COALESCE(PD.case_qty, 0)) AS allocated_qty 
      FROM Pallet_Details AS pd,
           Load_Product AS L,
           Products AS P,
           Run_Headers AS R
    WHERE PD.load_id = @load_id
      AND L.product_id = P.product_id
      AND P.product_id = R.product_id 
      AND PD.load_id = L.load_id 
      AND PD.run_id = R.run_id
     GROUP BY P.product_description, L.required_qty;



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by maddmike Thursday, November 28, 2013 2:48 AM
    Thursday, November 28, 2013 2:28 AM

All replies

  • Try the below:(Not tested)

    (if you provide DML and DDL, then we would be able to help you with working set of code.)

    Select  P.ProductDescription as Product, ISNULL(SUM(PD.CaseQty),0) as AllocatedQty, L.Qty as RequiredQty
    	From 	PalletDetail PD 
    	Left Join 
    	RunHeaders R on PD.RunId = R.RunId
    	Left Join
    	Products P on P.ProductId = R.ProductId
    	Left Join
    	LoadProduct L on L.ProductId = P.ProductId
    	Where 	PD.LoadId 	= @LoadId 
    	Group By L.LoadId, P.ProductId, P.ProductDescription, L.Qty



    Wednesday, November 27, 2013 6:22 AM
  • Still only one returns.
    Wednesday, November 27, 2013 6:26 AM
  • Try the latest one, I editted the query a bit to remove the condition from WHERE clause.
    Wednesday, November 27, 2013 6:28 AM
  • Could you provide sample data and expected output? and brief business logic would be great

    Satheesh
    My Blog


    Wednesday, November 27, 2013 6:28 AM
  • I've tracked my problem to the Where clause on And PD.LoadId = L.LoadId. when I pull this the function works however the allocated quantity displays as all quantity for every pallet not just the ones affiliated with that load.
    Wednesday, November 27, 2013 7:03 AM
  •  however the allocated quantity displays as all quantity for every pallet not just the ones affiliated with that load.

    Thats because, you have Qty in your GROUP by Clause. If you do not want that, then probably you can remove Qty from your group by and use SUM(Qty) in your Select list.

    Select  P.ProductDescription as Product, ISNULL(SUM(PD.CaseQty),0) as AllocatedQty, Sum(L.Qty) as RequiredQty
    	From 	PalletDetail PD 
    	Left Join 
    	RunHeaders R on PD.RunId = R.RunId
    	Left Join
    	Products P on P.ProductId = R.ProductId
    	Left Join
    	LoadProduct L on L.ProductId = P.ProductId
    	Where 	PD.LoadId 	= @LoadId 
    	Group By L.LoadId, P.ProductId, P.ProductDescription

    Wednesday, November 27, 2013 7:08 AM
  • I think I have an idea of the last possible option to make this work right, problem is I don't know more advanced functions in tsql. It seems the only way to get all rows is to wrap ISNULL(SUM(PD.CaseQty),0)asAllocatedQty

    Into some sort of exists statement, after my procedure over a few times the issue is that if no pallets have been assigned to the load(loadId) there is nothing selected for all 3 groups. Is there a way to do this?

    Wednesday, November 27, 2013 8:27 AM
  • It would be great if you can provide your sample DDL and DML to us with the Desired output. We would be able to help you better.


    Wednesday, November 27, 2013 8:35 AM
  • Tables relevant columns are as follows

    PalletDetail: RunId, LoadId, CaseQty

    RunHeaders: RunId, ProductId

    Products: ProductId, ProductDescription

    LoadProducts: LoadId, ProductId, ReqQty

    PalletDetail defines the pallet itself, RunHeaders defines the specifics of the run, Products defines the product the run is using, LoadProducts associates the qty of which products an order is required.

    Assuming a load requires 50 cases of product A and 40 cases of Product B and a pallet with 10 cases of Product A has been assigned to that load the desired output is:

    Product   |RequiredQty |AllocatedQty

    Product A|50                |10

    Product B|40                |0

    Problem with my current select statement is that if no pallets have been assigned to the load id the row for product B will not even appear

    Wednesday, November 27, 2013 8:51 AM
  • Again, you are not giving sample data for your tables. 

    Try the below:(Not tested)

    Select  P.ProductDescription as Product, ISNULL(SUM(PD.CaseQty),0) as AllocatedQty, Sum(L.Qty) as RequiredQty
    	From 	LoadProduct L
    	Left Join Products P on L.ProductId = P.ProductId
    	Left join PalletDetail PD on L.LoadID = PD.LoadID
    	Left Join RunHeaders R on PD.RunId = R.RunId
    	Where 	L.LoadId 	= @LoadId 
    	Group By L.LoadId, L.ProductId, P.ProductDescription

    Wednesday, November 27, 2013 9:03 AM
  • Apologies I'm not really good at asking for help. Here is a sample database script

    USE [testing]
    GO
    /****** Object:  Table [dbo].[LoadProduct]    Script Date: 11/27/2013 2:22:59 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[LoadProduct](
    	[LoadId] [int] NOT NULL,
    	[ProductId] [int] NOT NULL,
    	[Qty] [int] NOT NULL
    ) ON [PRIMARY]
    
    GO
    /****** Object:  Table [dbo].[PalletDetail]    Script Date: 11/27/2013 2:22:59 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[PalletDetail](
    	[PalletId] [bigint] IDENTITY(1,1) NOT NULL,
    	[RunId] [int] NOT NULL,
    	[LoadId] [int] NULL,
    	[CaseQty] [int] NOT NULL,
     CONSTRAINT [PK_PalletDetail] PRIMARY KEY CLUSTERED 
    (
    	[PalletId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    /****** Object:  Table [dbo].[Products]    Script Date: 11/27/2013 2:22:59 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Products](
    	[ProductId] [int] IDENTITY(1,1) NOT NULL,
    	[ProductDescription] [varchar](50) NOT NULL,
     CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
    (
    	[ProductId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[RunHeaders]    Script Date: 11/27/2013 2:22:59 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[RunHeaders](
    	[RunId] [int] IDENTITY(1,1) NOT NULL,
    	[ProductId] [int] NOT NULL,
     CONSTRAINT [PK_RunHeaders] PRIMARY KEY CLUSTERED 
    (
    	[RunId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    INSERT [dbo].[LoadProduct] ([LoadId], [ProductId], [Qty]) VALUES (1, 1, 75)
    GO
    INSERT [dbo].[LoadProduct] ([LoadId], [ProductId], [Qty]) VALUES (1, 2, 50)
    GO
    INSERT [dbo].[LoadProduct] ([LoadId], [ProductId], [Qty]) VALUES (1, 3, 20)
    GO
    INSERT [dbo].[LoadProduct] ([LoadId], [ProductId], [Qty]) VALUES (2, 2, 12)
    GO
    INSERT [dbo].[LoadProduct] ([LoadId], [ProductId], [Qty]) VALUES (2, 5, 10)
    GO
    INSERT [dbo].[LoadProduct] ([LoadId], [ProductId], [Qty]) VALUES (2, 1, 10)
    GO
    SET IDENTITY_INSERT [dbo].[PalletDetail] ON 
    
    GO
    INSERT [dbo].[PalletDetail] ([PalletId], [RunId], [LoadId], [CaseQty]) VALUES (1, 1, 1, 12)
    GO
    INSERT [dbo].[PalletDetail] ([PalletId], [RunId], [LoadId], [CaseQty]) VALUES (2, 1, NULL, 11)
    GO
    INSERT [dbo].[PalletDetail] ([PalletId], [RunId], [LoadId], [CaseQty]) VALUES (3, 1, NULL, 13)
    GO
    INSERT [dbo].[PalletDetail] ([PalletId], [RunId], [LoadId], [CaseQty]) VALUES (4, 2, NULL, 15)
    GO
    INSERT [dbo].[PalletDetail] ([PalletId], [RunId], [LoadId], [CaseQty]) VALUES (5, 2, NULL, 10)
    GO
    INSERT [dbo].[PalletDetail] ([PalletId], [RunId], [LoadId], [CaseQty]) VALUES (6, 2, NULL, 5)
    GO
    INSERT [dbo].[PalletDetail] ([PalletId], [RunId], [LoadId], [CaseQty]) VALUES (7, 3, NULL, 5)
    GO
    INSERT [dbo].[PalletDetail] ([PalletId], [RunId], [LoadId], [CaseQty]) VALUES (8, 3, NULL, 6)
    GO
    INSERT [dbo].[PalletDetail] ([PalletId], [RunId], [LoadId], [CaseQty]) VALUES (9, 3, NULL, 15)
    GO
    INSERT [dbo].[PalletDetail] ([PalletId], [RunId], [LoadId], [CaseQty]) VALUES (10, 4, NULL, 11)
    GO
    INSERT [dbo].[PalletDetail] ([PalletId], [RunId], [LoadId], [CaseQty]) VALUES (11, 4, NULL, 9)
    GO
    INSERT [dbo].[PalletDetail] ([PalletId], [RunId], [LoadId], [CaseQty]) VALUES (12, 4, NULL, 9)
    GO
    INSERT [dbo].[PalletDetail] ([PalletId], [RunId], [LoadId], [CaseQty]) VALUES (13, 5, NULL, 8)
    GO
    INSERT [dbo].[PalletDetail] ([PalletId], [RunId], [LoadId], [CaseQty]) VALUES (14, 5, NULL, 13)
    GO
    INSERT [dbo].[PalletDetail] ([PalletId], [RunId], [LoadId], [CaseQty]) VALUES (15, 5, NULL, 21)
    GO
    SET IDENTITY_INSERT [dbo].[PalletDetail] OFF
    GO
    SET IDENTITY_INSERT [dbo].[Products] ON 
    
    GO
    INSERT [dbo].[Products] ([ProductId], [ProductDescription]) VALUES (1, N'Product A')
    GO
    INSERT [dbo].[Products] ([ProductId], [ProductDescription]) VALUES (2, N'Product B')
    GO
    INSERT [dbo].[Products] ([ProductId], [ProductDescription]) VALUES (3, N'Product C')
    GO
    INSERT [dbo].[Products] ([ProductId], [ProductDescription]) VALUES (4, N'Product D')
    GO
    INSERT [dbo].[Products] ([ProductId], [ProductDescription]) VALUES (5, N'Product E')
    GO
    SET IDENTITY_INSERT [dbo].[Products] OFF
    GO
    SET IDENTITY_INSERT [dbo].[RunHeaders] ON 
    
    GO
    INSERT [dbo].[RunHeaders] ([RunId], [ProductId]) VALUES (1, 1)
    GO
    INSERT [dbo].[RunHeaders] ([RunId], [ProductId]) VALUES (2, 2)
    GO
    INSERT [dbo].[RunHeaders] ([RunId], [ProductId]) VALUES (3, 3)
    GO
    INSERT [dbo].[RunHeaders] ([RunId], [ProductId]) VALUES (4, 4)
    GO
    INSERT [dbo].[RunHeaders] ([RunId], [ProductId]) VALUES (5, 5)
    GO
    SET IDENTITY_INSERT [dbo].[RunHeaders] OFF
    GO
    ALTER TABLE [dbo].[LoadProduct]  WITH CHECK ADD  CONSTRAINT [FK_LoadProduct_Products] FOREIGN KEY([ProductId])
    REFERENCES [dbo].[Products] ([ProductId])
    GO
    ALTER TABLE [dbo].[LoadProduct] CHECK CONSTRAINT [FK_LoadProduct_Products]
    GO
    ALTER TABLE [dbo].[PalletDetail]  WITH CHECK ADD  CONSTRAINT [FK_PalletDetail_RunHeaders] FOREIGN KEY([RunId])
    REFERENCES [dbo].[RunHeaders] ([RunId])
    GO
    ALTER TABLE [dbo].[PalletDetail] CHECK CONSTRAINT [FK_PalletDetail_RunHeaders]
    GO
    ALTER TABLE [dbo].[RunHeaders]  WITH CHECK ADD  CONSTRAINT [FK_RunHeaders_Products] FOREIGN KEY([ProductId])
    REFERENCES [dbo].[Products] ([ProductId])
    GO
    ALTER TABLE [dbo].[RunHeaders] CHECK CONSTRAINT [FK_RunHeaders_Products]
    GO
    

    Wednesday, November 27, 2013 9:24 AM
  • Hope, my last thread is what you are looking for. If not, please let us know the desired output.
    Wednesday, November 27, 2013 9:39 AM
  • Sadly it also did not work, it seems all I can get is either the only product with pallets assigned or all pallets of that product type in allocatedqty. I appreciate all your help.
    Wednesday, November 27, 2013 9:55 AM
  • While searching I decided to play with union, it seemed to get me closer but only returned 2 of 3 rows in the sample database above. Code and pic follow.

    Select DISTINCT ISNULL(SUM(PD.CaseQty),0) as AllocatedQty, MAX(P.ProductDescription) as Product, MAX(L.Qty) as RequiredQty
    	From 	PalletDetail PD 
    	Left Join 
    	RunHeaders R on PD.RunId = R.RunId
    	Left Join
    	Products P on P.ProductId = R.ProductId
    	Left Join
    	LoadProduct L on L.ProductId = P.ProductId
    	Where 	L.LoadId 	= '1' And PD.RunId = R.RunId And R.ProductId = P.ProductId And R.ProductId = L.ProductId and L.LoadId = PD.LoadId
    	Group By P.ProductId, P.ProductDescription
    UNION
    Select 0 as AllocatedQty, MAX(P.ProductDescription), MAX(L.Qty)
    	From 	PalletDetail PD 
    	Left Join 
    	RunHeaders R on PD.RunId = R.RunId
    	Left Join
    	Products P on P.ProductId = R.ProductId
    	Left Join
    	LoadProduct L on L.ProductId = P.ProductId
    	Where 	L.LoadId 	= '1' And PD.RunId = R.RunId And R.ProductId = P.ProductId And R.ProductId = L.ProductId
    	
    	
    

    Wednesday, November 27, 2013 10:09 AM
  • Finally got it

    Select DISTINCT ISNULL(SUM(PD.CaseQty),0) as AllocatedQty, P.ProductDescription as Product, MAX(L.Qty) as RequiredQty
    	From 	PalletDetail PD 
    	Inner Join 
    	RunHeaders R on PD.RunId = R.RunId
    	Inner Join
    	Products P on P.ProductId = R.ProductId
    	Inner Join
    	LoadProduct L on L.ProductId = P.ProductId
    	Where 	L.LoadId 	= '1' And PD.RunId = R.RunId And R.ProductId = P.ProductId And R.ProductId = L.ProductId and L.LoadId = PD.LoadId
    	Group By P.ProductId, P.ProductDescription
    UNION
    Select 0 as AllocatedQty, P.ProductDescription, MAX(L.Qty)
    	From 	PalletDetail PD 
    	Inner Join 
    	RunHeaders R on PD.RunId = R.RunId
    	Inner Join
    	Products P on P.ProductId = R.ProductId
    	Inner Join
    	LoadProduct L on L.ProductId = P.ProductId
    	Where 	L.LoadId 	= '1' And PD.RunId = R.RunId And R.ProductId = P.ProductId And R.ProductId = L.ProductId
    	Group By ProductDescription

    Thank you all for your help

    • Marked as answer by maddmike Wednesday, November 27, 2013 10:24 AM
    • Unmarked as answer by maddmike Thursday, November 28, 2013 12:29 AM
    Wednesday, November 27, 2013 10:22 AM
  • Good to know you got solution, but still am in dark on your resultset.

    BTB, you can avoid DISTINCT as long as you have UNION.

    Wednesday, November 27, 2013 10:32 AM
  • Turns out I was too eager to be done, while running my solution today I found that I was getting Product A twice in the feed, I started playing around with it and have started to get the appropriate rows now but the Allocated Quantity is doubled and showing 24 while it should only show 12 . Here is that code

    Select * FROM (
    SELECT ISNULL(SUM(PD.CaseQty),0) as AllocatedQty, MAX(P.ProductDescription) as Product, MAX(L.Qty) as RequiredQty
    	From 	PalletDetail PD 
    	Inner Join 
    	RunHeaders R on PD.RunId = R.RunId
    	Inner Join
    	Products P on P.ProductId = R.ProductId
    	Inner Join 
    	LoadProduct L on L.ProductId = P.ProductId
    	Where 	PD.LoadId 	= '1' And PD.RunId = R.RunId And R.ProductId = P.ProductId
    	Group By P.ProductId, P.ProductDescription
    UNION
    Select 0 as AllocatedQty, P.ProductDescription, MAX(L.Qty) as RequiredQty
    	From 	LoadProduct AS L, Products AS P 
    	Where 	L.LoadId 	= '1' And L.ProductId = P.ProductId AND L.ProductId Not in 
    	(SELECT R.ProductId FROM PalletDetail as PD, RunHeaders as R
    		WHERE PD.LoadId = '1' AND PD.RunId = R.RunId)
    	GROUP BY P.ProductDescription) AS ABC

    Thursday, November 28, 2013 12:39 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you were pretty good). Temporal data should use ISO-8601 formats. Code should be in Standard SQL AS much AS possible and not local dialect. That means we use COALESCE(), not ISNULL() 

    This is minimal polite behavior on SQL forums. 

    I see that you so not use an industry standard like UPC, EAN, GTIN, etc for your products. You have a magical, vague “qty” in the Load_Products table; this is a violation of ISO-11179 because we have to have “<something in particular>_qty” in a valid data model. 

    Noobs who still think in sequential programming languages use the infixed INNER JOINs while experienced SQL programmers do not. Did you notice how many of your search conditions were repeated? It becomes obvious with a set-oriented syntax. 

    Why do you group by columns that are not in the SELECT? 

    Here is my guess without any specs or DDL. Does your boss treat you with this kind of contempt? 

    SELECT P.product_description, 
           L.required_qty,
          (SUM(COALESCE(PD.case_qty, 0)) AS allocated_qty 
      FROM Pallet_Details AS pd,
           Load_Product AS L,
           Products AS P,
           Run_Headers AS R
    WHERE PD.load_id = @load_id
      AND L.product_id = P.product_id
      AND P.product_id = R.product_id 
      AND PD.load_id = L.load_id 
      AND PD.run_id = R.run_id
     GROUP BY P.product_description, L.required_qty;



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by maddmike Thursday, November 28, 2013 2:48 AM
    Thursday, November 28, 2013 2:28 AM