Creating a complex stored-procedure to get multi-tables output

Question

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

Answer 1

Here's the code work with me:

For SQL Stored Procedure

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>
<a href="categories.aspx?pid=<%# DataBinder.Eval(Container.DataItem, "ParentID") %>&brandid=<%# DataBinder.Eval(Container.DataItem, "BrandID") %>"><%# DataBinder.Eval(Container.DataItem, "Title") %></a>
<%# DataBinder.Eval(Container.DataItem, "Summary") %>
<asp:Repeater id="rpSubCats" runat="server" DataSource='<%# GetSubCats(CInt(DataBinder.Eval(Container.DataItem, "ParentID")))%>'>
<ItemTemplate >
<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>
</ItemTemplate>
</asp:Repeater>
<div 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

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, wish that help