Grouping of data

[[deleted]]

I have a number of areas that can have subareas, I whant to present this areas in a indented list with each area with its subareas indeted under it.

ex.

Sweden
Ostersund
Brunflo
Ica
Norway
Narvik
Oslo

and so on...

The data comes from a table in MySQL with a relation to it self for which area it is situated within.

---

| areaid | inarea | name |

Anyone have a code snipppet to help me with this problem, I appriciate.

I've been at it for a few hours now and are totally locked up in my head and cant get it to work.

/Jonas

[[deleted]]

Obviusly the indentions didn't show on this messageboard so I explain with stars what I mean

a star = a space IRL

Sweden
Ostersund
Brunflo
Ica
Norway
Narvik
*Oslo

[kparker]

The first, and hardest, part is to get the query retrieving rows in the correct order. With mysql, and a self-relation, you probably can't do it in a single query (Oracle, DB2, and some of the other big boys have proprietary RECURSIVE extensions to sql that make it fairly easy to do this.)

But in mysql, you're probably stuck with issuing subqueries for each top- and mid-level item. Yuck, but at least retrieval performance on simple queries is something mysql is rightly noted for.

[[deleted]]

Yeap thats what I figured out as well and I have a couple of sulutions that will fix that problem but not the one with me loosing track of which child level I'm curreltly processing.

[peter48]

It's not so difficult if you have choosen a rigth code levels for areaid and inarea.

If you give more information, maybe can help.
Anyway, always can do two selects or one with GROUP BY , ORDER BY sentences of mysql.

Peter.