none
How to make table(s) for categories and sub categories and how to call it

    Question

  • I have a list of main categories and sub categories in which I am going to have a repeater load up the main categories and sub categories within each main category.

    I don't know what is the best way to start with SQL. I tried the following:

    Table1 (MainCategories)
    MainCatID (PK)
    MainCatName

    Table2 (SubCategories)
    SubCatID (PK)
    SubCatName
    MainCatID (FK)


    I started with that idea and then couldn't write a stored procedure to load the data up correctly, can anyone let me know what kind of a process can I do? I thought of using 1 table for all the main and sub categories but wasn't sure how I would do that.

    Thanks in advance.
    Tuesday, October 27, 2009 4:40 PM

Answers

  • Hi Elbasha,

    As Sean has pointed out your table structure looks fine, nothing wrong in it.  Thing I like to add - The result you are getting from whatever query you wrote is also fine and it is showing result as it is expected to.

    The issue you are talking about is a display issue, and I guess you should put this question on the forums of whatever front end application you are using.  SQL is not meant for handling display issues.

    WIth help of XML you can get all the subcats in comma separated format in single field, but I guess that is not what you want.


    If you are creating report out of, then all advance reporting tools can fulfill your requirement easily by simply grouping the report on Main Category column.

    Mangal Pardeshi  
    SQL With Mangal
    Friday, October 30, 2009 11:39 AM
    Moderator

All replies

  • I'm not entirely sure what you mean by load up with a repeater, but as for normalization what you have is a pretty standard setup. What were you looking for in terms of a stored procedure, maybe we can help?
    Tuesday, October 27, 2009 4:59 PM
    Answerer
  • Okay if my SQL tables are correct, I need the stored procedure to display something in this order:


    MainCat1
       - SubCat1
       - SubCat2

    MainCat2
      - SubCat3
      - SubCat4
      - SubCat5

    But the way I wrote the stored procedure it came as:

    MainCat1, SubCat1
    MainCat1, SubCat2

    MainCat2, SubCat3
    MainCat2, SubCat4
    MainCat2, SubCat5

    Which is putting the MainCat next to every SubCat, but I want the MainCat1 and MainCat2 to come up once only
    Tuesday, October 27, 2009 6:26 PM
  • ElBasha,

    Are you looking for this in SP form?

    CREATE TABLE #Categories(
    CatId	int	not null primary key,
    CatName	varchar(30) null,
    CatDesc	varchar(30) null)
    
    CREATE TABLE #SubCat(
    SubCatId	int not null primary key,
    MainCatId	int not null,
    SubCatName	varchar(30)
    )
    
    INSERT INTO #Categories(CatId, CatName, CatDesc) VALUES 
    (1, 'MainCat1', 'Main #1'),
    (2, 'MainCat2', 'Main #2'),
    (3, 'MainCat3', 'Main #3')
    
    INSERT INTO #SubCat(SubCatId, MainCatId, SubCatName) VALUES
    (1, 1, 'SubCat1'),
    (2, 1, 'SubCat2'),
    (3, 1, 'SubCat3'),
    (4, 2, 'SubCat4'),
    (5, 2, 'SubCat5'),
    (6, 2, 'SubCat6'),
    (7, 3, 'SubCat7'),
    (8, 3, 'SubCat8')
    
    Declare @CatId int = 3
    
    
    Select CatName FROM #Categories WHERE CatId = @CatId
    UNION ALL
    SELECT sc.SubCatName as [CatName] FROM #SubCat sc WHERE sc.MainCatId = @CatId
    
    
    DROP TABLE #Categories
    DROP TABLE #SubCat
    Wednesday, October 28, 2009 11:31 AM
    Answerer
  • Thank you for replying back Sean, that is almost what I need.  The only problem is that I don't want any specific CatID to show up.  I want all of the Categories and their Sub Categories to show up.  On my repeater (ASP.NET), I have the following:

    <asp:Repeater ID="rptrMainCategories" runat="server" DataSourceID="SqldsMainCategories">
    <ItemTemplate>
    <li>
    <a href="#"><%#Eval("MainCategoryName")%></a>
    <ul >
    <li><a href="#"><%#Eval("SubCategoryName")%></a></li>
    </ul>
    </li>
    </ItemTemplate>
    </asp:Repeater>

    I would like the results to come up as

    MainCat1
       - SubCat
       - SubCat
    MainCat2
       - SubCat


    I tried to remove the Where CatID = @CatID in your code but that didn't seem to solve the problem, hopefully you can help me out.

    Thanks in advance.
    Wednesday, October 28, 2009 5:00 PM
  • ElBasha,

    I'm sorry I'm not very good with ASP, but from a logic standpoint I'm not sure it would be prudent to have a procedure or function to return the entire structure. Personally I can't find a good way to do this without some extra steps - maybe someone can help that is better with recursive CTEs than I am.

    This can be sent through using XML from SQL Server in a tree list as you would like, however I'm not sure if that's part of your design. Also, hierarchial types are best rendered in the application rather than SQL Server.

    The last I can really input on this is that there isn't a really good way that I know of to do this other than XML or a stored procedure that has a good deal of extra logic in it to deal with the recursiveness of the data.

    Any other input would be appreciated from some of the Admins/MVP's.

    Sorry I can't help more,
    -Sean
    Thursday, October 29, 2009 12:45 PM
    Answerer
  • Hi Elbasha,

    As Sean has pointed out your table structure looks fine, nothing wrong in it.  Thing I like to add - The result you are getting from whatever query you wrote is also fine and it is showing result as it is expected to.

    The issue you are talking about is a display issue, and I guess you should put this question on the forums of whatever front end application you are using.  SQL is not meant for handling display issues.

    WIth help of XML you can get all the subcats in comma separated format in single field, but I guess that is not what you want.


    If you are creating report out of, then all advance reporting tools can fulfill your requirement easily by simply grouping the report on Main Category column.

    Mangal Pardeshi  
    SQL With Mangal
    Friday, October 30, 2009 11:39 AM
    Moderator