Complex Stored Procedure .. Please Help RSS

• • Reply
  

  hsobhy

  Member

  73 Points

  79 Posts

  Complex Stored Procedure .. Please Help

  Oct 10, 2012 06:58 PM|LINK

  Hi everyone,

  I have product catalog that have two levels of categories and brands associated with the products.

  Tables Like:

  Products : ProductID | CategoryID | BrandID

  Categories : CategoryID | ParentID | Image(bit)

  What I need is while browsing the brands pages I select all "Parent Categories" that are parents for categories associated with products added already under this brand using a Stored Procedure... and make sure to get the value of the Image column

  What I Used
  =========

  CREATE PROCEDURE [dbo].[GetBrandProductsCats]
  @iLanguageID int,
  @iBrandID int
  As
  Begin
  SELECT C.Image, C.CategoryID, C.ParentID,CD.Title, CD.Summary, BrandID = @iBrandID  FROM Categories C
  Join CategoryData CD on C.ParentID = CD.CategoryID
  WHERE C.CategoryID in (SELECT Products.CategoryID  FROM Products WHERE Products.BrandID = @iBrandID)
  
  End
  GO

  This actually worked but the Image column value is not for the parent category

  Any Ideas ? wish you have one cause I got tired :)

  Thank you all in advance
  
  

  StoredProcedure .NET4 sql2005

  nolimits
• • Reply
  

  Dan Bracuk

  Contributor

  3970 Points

  1096 Posts

  Re: Complex Stored Procedure .. Please Help

  Oct 10, 2012 07:14 PM|LINK

  Use a self join.

  select etc

  from products p join categories c1 on p.categoryid = c1.categoryid

  join categories c2 on c1.parentid = c2.categoryid

  etc

• • Reply
  

  hsobhy

  Member

  73 Points

  79 Posts

  Re: Complex Stored Procedure .. Please Help

  Oct 10, 2012 07:25 PM|LINK

  Hello Dan,

  Thanks for your reply .. maybe i didn't get it . could you please give an examole using my code .. many thanks :)

  Again, I'm selecting Parent Categories that have sub-categories In the CategoryID column of the products which have the BrandID ... :(

  Thank you

  nolimits
  • Edited by hsobhy on Oct 10, 2012 07:38 PM
• • Reply
  

  Dan Bracuk

  Contributor

  3970 Points

  1096 Posts

  Re: Complex Stored Procedure .. Please Help

  Oct 10, 2012 07:55 PM|LINK

  hsobhy

  Hello Dan,

  Thanks for your reply .. maybe i didn't get it . could you please give an examole using my code .. many thanks :)

  Again, I'm selecting Parent Categories that have sub-categories In the CategoryID column of the products which have the BrandID ... :(

  I'm doing the same thing differently.  This line gives you access to the sub-categories for the brand id.

                    from products p join categories c1 on p.categoryid = c1.categoryid

  And this line gives you access to the parent category which contains your image data

                  join categories c2 on c1.parentid = c2.categoryid

   

• • Reply
  

  hsobhy

  Member

  73 Points

  79 Posts

  Re: Complex Stored Procedure .. Please Help

  Oct 10, 2012 08:43 PM|LINK

  Dan Bracuk

  I'm doing the same thing differently.  This line gives you access to the sub-categories for the brand id.

                    from products p join categories c1 on p.categoryid = c1.categoryid

  And this line gives you access to the parent category which contains your image data

                  join categories c2 on c1.parentid = c2.categoryid

   

  Here's the output

  CREATE PROCEDURE [dbo].[GetBrandProductsCats]
  @iLanguageID int,
  @iBrandID int
  As
  Begin
  SELECT CD.Title, CD.Summary, BrandID = @iBrandID, C.Image, C.CategoryID, C.ParentID FROM Products P
  Join Categories C on P.CategoryID = C.CategoryID
  Join CategoryData CD on C.ParentID = CD.CategoryID
  WHERE C.CategoryID in (SELECT Products.CategoryID  FROM Products WHERE Products.BrandID = @iBrandID)
  
  End
  GO

  This returns completely unexpected results, It returns the Parent repeated 3 times (number of products) without looking to the brandID

  Thank you dan for trying with my fresh experience :)

  nolimits
• • Reply
  

  hsobhy

  Member

  73 Points

  79 Posts

  Re: Complex Stored Procedure .. Please Help

  Oct 13, 2012 12:39 PM|LINK

  Hello,

  Is it really that hard? any other techniques?

  Thank You,

  StoredProcedure .NET4 sql2005

  nolimits
• • Reply
  

  hsobhy

  Member

  73 Points

  79 Posts

  Re: Complex Stored Procedure .. Please Help

  Oct 13, 2012 09:24 PM|LINK

  Hello,

  I Used this for the Image:

  (SELECT Categories.Image FROM Categories WHERE Categories.CategoryID = C.ParentID) as Image

  so the final worked with me as follow:

  CREATE PROCEDURE [dbo].[GetBrandProductsCats]
  @iLanguageID int,
  @iBrandID int
  As
  Begin
  SELECT CD.Title, CD.Summary, BrandID = @iBrandID, (SELECT Categories.Image FROM Categories WHERE Categories.CategoryID = C.ParentID) as Image, C.CategoryID, C.ParentID FROM Categories C
  Join CategoryData CD on C.ParentID = CD.CategoryID 
  WHERE C.CategoryID in (SELECT Products.CategoryID  FROM Products WHERE Products.BrandID = @iBrandID)
  
  End
  GO
  

  
  Thanks for you all

  StoredProcedure .NET4 sql2005

  nolimits
  • Edited by hsobhy on Oct 13, 2012 09:25 PM
  • Edited by hsobhy on Oct 13, 2012 09:29 PM
  • Edited by hsobhy on Oct 21, 2012 12:39 AM
• • Reply
  

  hsobhy

  Member

  73 Points

  79 Posts

  Re: Complex Stored Procedure .. Please Help

  Oct 20, 2012 07:27 PM|LINK

  Hello,

  I still Can't do it .. it didn't work .. any ideas?

  Thank you all,

  StoredProcedure .NET4 sql2005

  nolimits
  • Edited by hsobhy on Oct 20, 2012 07:27 PM
  • Edited by hsobhy on Oct 21, 2012 12:39 AM
  • Edited by hsobhy on Oct 21, 2012 12:39 AM
  • Edited by hsobhy on Jan 10, 2013 02:17 PM
  • Edited by hsobhy on Jan 10, 2013 02:17 PM
• • Reply
  

  hsobhy

  Member

  73 Points

  79 Posts

  Re: Complex Stored Procedure .. Please Help

  Jan 10, 2013 02:16 PM|LINK

  Hi all,

  Here's the code worke with me:

  For SQL Stored Procceder
  =========================

  CREATE PROCEDURE [dbo].[GetBrandProductsCats]
  @iLanguageID int,
  @iBrandID int
  
  As
  Begin
  select C.CategoryID,C.ParentID, BrandID = @iBrandID, C.Image, CD.Title, CD.Summary from Categories C
  Join CategoryData CD on C.CategoryID = CD.CategoryID
  Join BrandsCategories BC on BC.CategoryID = C.CategoryID and  BC.BrandID = @iBrandID
  Where C.ParentID=0 and C.Status=1 and CD.LanguageID=@iLanguageID
  End
  GO

  
  
  

  For The Code:
  ========================

  I used parent ID from the stoored to get products cats and it's image and made a function for sub cats:

  <asp:Repeater id="rpCats" runat="server" >
    <ItemTemplate>
  <div class="listrow">
    <div class="listtitle"><a href="categories.aspx?pid=<%# DataBinder.Eval(Container.DataItem, "ParentID") %>&brandid=<%# DataBinder.Eval(Container.DataItem, "BrandID") %>"><%# DataBinder.Eval(Container.DataItem, "Title") %></a></div>
  </div>
  <div class="listrow">
    <div class="listrowInfo" id="tblData" runat="server">
    <%# DataBinder.Eval(Container.DataItem, "Summary") %>
      <asp:Repeater id="rpSubCats" runat="server" DataSource='<%# GetSubCats(CInt(DataBinder.Eval(Container.DataItem, "ParentID")))%>'>
      <ItemTemplate >
        <div class="InfoRow1">
          <span class="TxtRightArrowSm"> » </span><a href='products.aspx?pid=<%# DataBinder.Eval(Container.DataItem, "ParentID") %>&cid=<%# DataBinder.Eval(Container.DataItem, "CategoryID") %>&brandid=<%# DataBinder.Eval(Container.DataItem, "BrandID") %>'><%# DataBinder.Eval(Container.DataItem, "Title") %></a>
        </div>
      </ItemTemplate>
    </asp:Repeater>
    </div>
    <div class="listrowPic" id="tblPic" runat="server">
      <asp:Image id="imgThumb" runat="server" Borderwidth="0" Width="70px"></asp:Image>
  </div>
  <asp:TextBox id="txtImage" runat="server" Visible="False" Text='<%# DataBinder.Eval(Container.DataItem, "Image") %>'></asp:TextBox>
  <asp:TextBox id="txtID" runat="server" Visible="False" Text='<%# DataBinder.Eval(Container.DataItem, "ParentID") %>'></asp:TextBox>
  </ItemTemplate>
  </asp:Repeater>

  Here is the GetSubCats Function
  ========================

      '''' -----------------------------------------------------------------------------
      '''' <summary>
      '''' GetSubCats gets sub-cats for each parent
      '''' </summary>
      '''' -----------------------------------------------------------------------------
      Public Function GetSubCats(ByVal intParentID As Integer) As DataView
  
          If Request("brandid") <> "" Then
  
              Dim objDB As New ProductsDB
              Dim dsSubCats As New DataSet
              objDB.daGetBrandProductsSubCats.SelectCommand.Parameters("@iParentID").Value = intParentID
              objDB.daGetBrandProductsSubCats.SelectCommand.Parameters("@iLanguageID").Value = System.Configuration.ConfigurationManager.AppSettings("LanguageID")
              objDB.daGetBrandProductsSubCats.SelectCommand.Parameters("@iBrandID").Value = Request("brandid")
              objDB.daGetBrandProductsSubCats.Fill(dsSubCats)
              Dim dvSubCats As DataView = dsSubCats.Tables(0).DefaultView
              GetSubCats = dvSubCats
  
          End If
  
      End Function

  Thank you all,

  nolimits
  • Marked as answer by hsobhy on Jan 10, 2013 02:17 PM