Simple SQL question..

[Syringer]

I have a product lookup table where product groups can reference other groups as parents, thus:

PRODUCT_GROUP_ID
PRODUCT_NAME
PRODUCT_GROUP_PARENT_ID

so
1 Alcohol
2 Beer 1
3 German 2
4 French 2

etc...

and it goes 4-5 levels deep. Does anyone know a simple query that would return the ID, Name and parent group ID if given a level as a parameter? So for example, if I called this query with group level 1, it would give me German beer with a parent_group_id of 2.

 

[Argo]

Look at Oracle's "CONNECT BY" keyword. Don't know if any other DBs support it.

 

[Superwormy]

You should look into using the Nested Set Model for SQL. It makes nested categories significantly easier to deal with.

I highly reccomend the book: Joe Celko's SQL For Smarties: Advanced SQL Programming. It has a *lot* of information on this model. Or Google for Nested Set Model.

 

[Markbnj]

This could easily be done in application code or in a stored procedure. Nested sets is certainly what I'd be looking at first for doing it in a single query, but I don't have much experience with them. Essentially...

A = select id from table_name where parent_id = null

gives you the set that is level 0. All of level 1 has a parent_id that is in level 0, so...

select * from table_name where parent_id in (select id from table_name where parent_id = null)

gives you level 1, and so on. The only issue with arbitrary depth is looping through to nest the query the right number of times. Fortunately you know that up front.

 

[KLin]

If it's SQL 2005, you can use a CTE to create a recursive query. :thumbsup: