SQL query problem

product categories and product subcategries in one table

[BigHit]

Hi there :)

This is for an ecommerce site so I hope its ok to post in this forum......

I'm having trouble with how to display some text from one of my tables in my .NET admin panel...

- The table is called Category and also contains Sub Categories by use of a parentID.
- The categories will be used to organise products for an ecommerce site.
- The links will go 2 links deep, therefore each product will belong to a subcategory which then belongs to a category.
- For example, a particular bike will be under a manufacturers name (sub category), which will be under the Bikes category.

This is done by setting the table up as follows:

categoryID___name___parentID
1___Bikes________0
2___Clothing______0
3___Components___0
4___Red Bikes_____1
5___Green Bikes____1
6___Blue Bikes_____1
7___Jackets_______2
8___Shorts________2
9___Chains________3
10__Pedals________3

So, as you can see, items 1-3 are Categories as they don't have a parentID and items 4-10 are Sub Categories as they have a parentID that refers to a Categories categoryID.

Sorry if that a long winded explanations. Heres the problem.....

In my admin panel I need to display all the categories and also their parent category. At the moment I have a list of all the categories and their parentID, but I really need to display the name (real data) rather than just a value.

So instead of displaying the above, I need to display:

1___Bikes________NO PARENT
2___Clothing______NO PARENT
3___Components___NO PARENT
4___Red Bikes_____Bikes
5___Green Bikes____Bikes
6___Blue Bikes_____Bikes
7___Jackets_______Clothing
8___Shorts________Clothing
9___Chains________Components
10__Pedals________Components

I hope I'm not being stupid by not seeing the solution to this.

Any ideas?

Many thanks indeed :)

[danieljean]

Hmm... I have something similar that works. Do you mind adding 0 - ROOT?

I believe the query would end up looking like this:

SELECT cat.categoryID, cat.name, cat2.name
FROM category as cat
INNER JOIN category as cat2
ON cat2.parentID = cat.categoryID

[BigHit]

Hi there, thanks for the reply. No, I don't mind adding O ROOT. However, do you really think this table is good database architecture. I'm starting to wonder if a sub category table would be a better solution. Or does using the parentID column have any particular advantages?

Thanks for the code, I'll give it a go :)

[danieljean]

This is better than having a separate table - what happens when you want to go 3 or 4 levels deep?

[john_k]

Whether the root is virtual or exists in the table is not too critical (if you are certain of your future needs). My preference, after doing several systems both ways, is to put it into the data table. But rather than keying on the category id, the root is denoted by having a parentid of NULL.

You will need to have special handling in some of your program code either way. If you don't include the root as actual data, then some of your code will need to be hard coded to know that 0 is the top dog. Your code will also have to contain knowlege of the name or any other data fields normally kept in the table.

If you do include it, then some of your code will need to know to not display it.

Over time, I think you will find yourself coding in more exception handling to accomodate the root not being actual data.

Another benefit of having the root (identified as any node having NULL for a parentid) in the physical database is that you can more easily maintain two or more distinct hierarchies with no connections.

If you ever go to a hierarchy that is not always 2-deep, then having a virtual root will definitely cause more issues with any functionality that needs to traverse your category tree.