SQL Question

[Yahweh]

Hopefully, there is someone who knows how to solve this, I've been working on it for a few hours and I've got myself absolutely nowhere...

I have a database (built in MSAcess), it has a series of records (which if I understand databases correctly, that's normal).

Each record can be assigned a ParentID, which is the ID of another record.

My table looks like this:

Table1
<table width="70%" cellpadding="1" cellspacing="0" border="1" style="font-size:9pt;font-family:Courier New;">
	<tr>
		<td bgcolor="#c0c0c0" width="10%">[b]ID[/b]
(Autonumber)</td>
		<td bgcolor="#c0c0c0" width="70%">[b]Name[/b]
(Text)</td>
		<td bgcolor="#c0c0c0" width="20%">[b]ParentID[/b]
(Number)</td>	

	</tr>
	<tr>
		<td>1</td>
		<td>Main Directory</td>
		<td>NULL</td>
	</tr>
	<tr>
		<td>2</td>
		<td>Yahweh</td>
		<td>1</td>
	</tr>
	<tr>
		<td>3</td>
		<td>ASP Files</td>
		<td>2</td>
	</tr>
	<tr>
		<td>5</td>
		<td>shanek</td>
		<td>1</td>
	</tr>
	<tr>
		<td>6</td>
		<td>Eos of the Eons</td>
		<td>1</td>
	</tr>
	<tr>
		<td>7</td>
		<td>Cool Libertarian Stuff</td>
		<td>5</td>
	</tr>
	<tr>
		<td>8</td>
		<td>Even Cooler Libetarian Stuff</td>
		<td>7</td>
	</tr>
	<tr>
		<td>4</td>
		<td>Pretty Ponies</td>
		<td>2</td>
	</tr>
	<tr>
		<td>9</td>
		<td>Anti-Homeopathy Illuminati</td>
		<td>6</td>
	</tr>
	<tr>
		<td>10</td>
		<td>Totally Macho Libertarian Stuff</td>
		<td>8</td>
	</tr>	
</table>

I would like to take the contents of that table and arrange it into a hierarchy structure. The output would look like this:

Main Directory
....Yahweh
........ASP Files
........Pretty Ponies
....shanek
........Cool Libertarian Stuff
............Even Cooler Libertarian Stuff
................Totally Macho Libertarian Stuff
....Eos of the Eons
........Anti-Homeopathy Illuminati

As well as listing one record's lineage like this:

(Listing lineage of record #8)

Main Directory > shanek > Cool Libertarian Stuff > Even Cooler Libertarian Stuff

Ideally, I'd like to take that minimal amount of information necessary (all the information shown in the table above), and have it display the hierarchy, however SQL has decided it doesnt want to cooperate with Yahweh.

It seems all the little tutorials I've read do not give me solutions which are easily implemented.

Me and SQL do not get along very well, it seems everything more sophisticated than SELECT, UPDATE, INSERT, DELETE only hurts SQL's feelings, making it give me completely vauge "Syntax Error on Line 72" complaints.

It seems that something like this can be solved with a recursive function, that is much easier said than done with the example table I've given.

Preferably, I'd like to see how this can be solved using ASP, although I could probably figure out what I'm doing just as soon as I know the table is ordered correctly.

If anyone can help me out, I would appreciate it.

 

[ceptimus]

You could restructure the database with extra tables. It looks as though you really want the data in the form of a doubly linked list or binary tree.

The system you have could work with a small number of records, but will slow down a lot as the database grows. If you go for the tree structure, there will be more overhead when adding new records (and especially when removing records) but the queries will execute much quicker.

How many records do you expect to populate the database with?

 

[scribble]

Each record can be assigned a ParentID, which is the ID of another record.

I don't use MS access, but that's exactly the right way to do it in Oracle and the like. Good job - now to get it to output the way you want you need to perform a hierarchical query. In Oracle's PL/SQL this is done using a "start with" and "connect by" clause - does your SQL support that?

Edit to add: here's a link that explains it as well as I could and even uses a similar technique (to the one I'd have demonstrated) to indent the output:

http://www.adp-gmbh.ch/ora/sql/connect_by.html

And the google cache of same, because it's not coming up for me:

http://216.239.41.104/search?q=cach...nnect_by.html+SQL+"connect+by"&hl=en&ie=UTF-8[/url]

The system you have could work with a small number of records, but will slow down a lot as the database grows. If you go for the tree structure, there will be more overhead when adding new records (and especially when removing records) but the queries will execute much quicker.

I'm no MS Access engineer, what you say may be true for their shoddy database. In general, however, connect by is fast, fast, fast. Just index on the connections for a even faster fast.

Now - I notice in your hierarchy you are storing what appear to be distinct *types* of information. (People vs. books) and those should almost definately be split into two tables, so a restructuring may be in order - but not just to accomplish the hierarchy you want. That you've done.

 

[Reb]

Unfortunately, JetSQL does not support the SQL clauses that scribble mentioned, so your best bet is to do in ASP using a cursor and a recursive subroutine, like this:

dim cn, rs, sSQL
set cn=createobject("adodb.connection")
cn.open "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=p:\ath\to\database.mdb"
sSQL = "Select ID,[Name],ParentID FROM table1 " & _
"order by ParentID, ID"
'for debugging
'Response.Write sSQL
'comment the previous line when finished debugging
set rs=CreateObject("adodb.recordset")
rs.CursorLocation = 3 'adUseClient
rs.LockType = 1 'adLockReadOnly
rs.Open sSQL, cn,,,1
'disconnect the recordset:
set rs.ActiveConnection=nothing
cn.Close: set cn = nothing

if rs.EOF then
Response.Write "no records returned"
else
dim curID, oDiv
curID=rs(0).Value
Response.Write "<div>"
Response.Write rs(1).Value
WriteChildDivs rs.Clone,curID,0
rs.Close:set rs = nothing
end if

Sub WriteChildDivs(pRS,pID,pIndent)
pRS.Filter="ParentID=" & pID
do until pRS.EOF
Response.Write "<div style=""margin-left:" & _
pIndent + 10 & "px"">"
Response.Write pRS(1).Value
WriteChildDivs pRS.clone, pRS(0).Value, pIndent + 10
Response.Write "</div>"
pRS.MoveNext
loop
set pRS=nothing
End Sub

Hope this helps,
Reb

 

[Reb]

Arrgh!
It didn't display my html tags. Maybe if I encode the angle brackets ...

dim cn, rs, sSQL
set cn=createobject("adodb.connection")
cn.open "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=p:\ath\to\database.mdb"
sSQL = "Select ID,[Name],ParentID FROM table1 " & _
"order by ParentID, ID"
'for debugging
'Response.Write sSQL & "<BR>"
'comment the previous line when finished debugging
set rs=CreateObject("adodb.recordset")
rs.CursorLocation = 3 'adUseClient
rs.LockType = 1 'adLockReadOnly
rs.Open sSQL, cn,,,1
'disconnect the recordset:
set rs.ActiveConnection=nothing
cn.Close: set cn = nothing

if rs.EOF then
Response.Write "no records returned"
else
dim curID curID=rs(0).Value
Response.Write "<div>"
Response.Write rs(1).Value
WriteChildDivs rs.Clone,curID,0
rs.Close:set rs = nothing
Response.Write "</div>"
end if

Sub WriteChildDivs(pRS,pID,pIndent)
pRS.Filter="ParentID=" & pID
do until pRS.EOF
Response.Write "<div style=""margin-left:" & _
pIndent + 10 & "px"">"
Response.Write pRS(1).Value
WriteChildDivs pRS.clone, pRS(0).Value, pIndent + 10
Response.Write "</div>"
pRS.MoveNext
loop
set pRS=nothing
End Sub

Yes, that worked
Edited to fix a bit of sloppiness

 

[michaellee]

originally posted by Yahweh.

although I could probably figure out what I'm doing just as soon as I know the table is ordered correctly.

"SELECT T1.NAME AS MAIN, TABLE1.NAME AS SUB FROM TABLE1 AS T1 INNER JOIN TABLE1 ON T1.ID = TABLE1.PARENTID;"

Run this statement, and your records are ordered correctly.

 

[Wudang]

Yahweh, if you want to learn SQL the best book I've seen is Joe Celko's "SQL for Smarties: advanced SQL programming". It avoids the usual meandering examples and got me started thinking of SQL in terms of set theory. I think you'd gobble it up. He has a large section in that book on implementing binary trees in SQL.

 

[Reb]

"SELECT T1.NAME AS MAIN, TABLE1.NAME AS SUB FROM TABLE1 AS T1 INNER JOIN TABLE1 ON T1.ID = TABLE1.PARENTID;"

Run this statement, and your records are ordered correctly.

Without an ORDER BY clause, you cannot be sure of the sort order. Yahweh, I assume this is the sort order you are after:

ORDER BY T1.ID,TABLE1.ID

You will still need a recursive function or procedure to get these names displayed in the nested fashion you are after.

Reb

 

[Yahweh]

Thank you for the responses, this has been very helpful

I'll implement some of the solutions in just a bit, but now I'll see what I can do El Greco in this thread.

 

[epepke]

If you were using Oracle, you could use CONNECT.

However, you aren't, so no dice.

Best thing, probably, would be a series of bottom-up or top-down traversals.

 

[michaellee]

originally posted by Reb

Without an ORDER BY clause, you cannot be sure of the sort order. Yahweh, I assume this is the sort order you are after:

Incorrect. Regardless of where the data exists in the table, as long as Yahweh uses the ID and ParentID fields as in his example, the query I provided will always result in the correct nested fashion.
The SUB field:
Lists all the Main Directory NAMEs first.
Then it lists the next lowest branch NAMEs, and so on down to the NAMEs in the lowest branch. Each NAME in each branch is linked to its next higher up branch by the MAIN field. There is no need for any further function or procedure in order to display the structure Yahweh provided in his example.

 

[Reb]

Originally posted by michaellee
Incorrect. Regardless of where the data exists in the table, as long as Yahweh uses the ID and ParentID fields as in his example, the query I provided will always result in the correct nested fashion.

Better go back to Celko
In a relational database, a table is defined as a set of unordered rows. The database engine can store the rows in whatever order is convenient. Depending on the rdbms, the physical sort order may be close to the logical sort order, but one can never be sure. The only way to guarantee the order of the rows being selected from a table is to use an ORDER BY clause.

This is emphasized in every SQL language book I've read.

I will grant you that Access is not as compliant to relational theory as other rdbms's, but it is not a bad idea to to get into good habits before moving to other rdbms's that are more compliant.

The SUB field:
Lists all the Main Directory NAMEs first.
Then it lists the next lowest branch NAMEs, and so on down to the NAMEs in the lowest branch. Each NAME in each branch is linked to its next higher up branch by the MAIN field. There is no need for any further function or procedure in order to display the structure Yahweh provided in his example.

Wait a minute - you are talking as if there was some sort of loop being done by the query engine. This is not the case: SQL performs set-based operations, not sequential.

Bottom-line: if the records are retrieved in the desired order (and I'm not saying they won't be - since it is Access, they very likely will be), it is more due to luck than anything else.

Reb