Table format outputing in self-joined table data

[b]data in myTable[/b]

[COLOR="Blue"](id) mom geo[/COLOR]
(1)   0  Asia
(2)   0  Europe
(3)   1  India
(4)   0  Africa
(5)   2  France
(6)   2  Germany
(7)   1  Korea
(8)   7  Seoul
(9)   4  Egypt
(10)  5  Paris
(11)  9  Cairo
(12)  8  Namsan

I have a data in myTable like the above.

I like to output the root records which have mom=0 in the 1st line.
I like to output the 2nd joined records which is child of the root records in the 2nd line.
I like to output the 3rd joined records which is child of the 2nd records in the 3rd line.
: :
: :

My target result is at http://dot.kr/x-test/tree1.cfm

How can I make the target result above with the data in myTable in coldFusion?

How can I make the target result above with the data in myTable in coldFusion?

You don’t because this is an SQL issue not a ColdFusion one ;). That aside I think your problem would be easily solved by splitting this into two tables as you’ve got a one-to-many relationship going on here ( ONE Continent has MANY Countries ).

Table 1 - Name: Continents

id geo
0 Asia
1 Europe
2 Africa

Table 2 - Name: Country

id geo cid
0 France 1
1 Germany 1
2 Egypt 2
.
.
.

To get all the countries you would do an SQL join statement based on a relationship between the Continents id field and Country cid field.

Theory here - http://en.wikipedia.org/wiki/Join_(SQL)

You’ve also got cities there so you might want another table for “cities” table which has a relationship with Countries.

Through that you could then do queries like - “What cities are in the Continent of Europe?” :wink:

It should be possible as one table by using a table alias

see Categories and Subcategories

the query is in there, and even the coldfusion is discussed