Multiple SELECT from nested queries

[JeepStone]

I'm using Access/ASP

I have a navigation heirarchy which is structured as follows (simplified):

Parent Child
0 1
1 2
2 3

The ID's of these are joined with the contents of the site. I'm trying to develop a 'breadcrumb' navigation section so I get

News>Latest>Some Article

My current query is:
SELECT tc.ID, tc.Template, tc.title AS ['level three'],
( tc2.title FROM t_contents AS tc2 WHERE tc2.id = th.parent) AS ['level two'],
(SELECT tc3.title FROM t_contents AS tc3 INNER JOIN t_heirarchy AS th3 ON tc3.id = th3.id WHERE tc3.id = (SELECT th4.parent FROM t_heirarchy AS th4 WHERE th4.id = th.parent)) AS ['level one']
FROM t_contents AS tc INNER JOIN t_heirarchy AS th ON tc.id=th.id
WHERE tc.id=37;

The problem is, as soon as I try to SELECT more than one field in the sub queries, I get an error saying I'm not allowed to SELECT multiple fields in a sub query.

How do I get around this so that I can SELECT the sub level ID's and Templates?

Thanks in advance

Pete

 

[r937]

pete, i can fix your query problem easily, it's just a matter of the right syntax for the join

however, i can't quite picture what links to what, and in particular i think your Parent/Child illustration is throwing me off

how is t_contents related to t_heirarchy?

"News" and "Latest" are category names rather than article names, so i kind of get the impression that you have stored category names in the t_contents table, where i assume the article contents are

also, for your breadcrumb trail, do you want to start at the top of the hierarchy, or list an article and "go up" from there?

rudy

http://rudy.ca/

 

[JeepStone]

Thanks for the reply rudy,

t_heirarchy is the structure of the site:
Parent Child
0 1
0 2
0 3
1 10
1 11

i.e. A Parent ID of 0 is the top level (Home). It has 3 children. In this example, Child ID = 1 has two further levels below it.

t_contents is the main content of the site:
ID Title Description Template
1 blah blah desc 10
2 blah2 blah2 desc 10
10 blah10 blah10 desc 99

I need to get the ID, Title (which appears in the breadcrumb) and the Template for each level of the navigation and display it

Home>>blah>>blah10

I hope that makes it clearer? I have tried the following as well:

SELECT c1.ID, c1.Title, c1.Template
FROM ((t_contents AS c1 INNER JOIN t_heirarchy AS h1 ON c1.ID = h1.ID)
LEFT JOIN t_contents AS c2 ON h1.ID = c2.ID )
where c2.id =
(SELECT c2.ID, c2.Title, c2.Template
FROM ((t_contents AS c2 INNER JOIN t_heirarchy AS h2 ON c2.ID = h2.ID)
LEFT JOIN t_contents AS c3 ON h2.ID = c3.ID))

but again I get errors when I SELECT more than one value from the sub query.

 

[r937]

select L1.ID as L1ID,
, L1.Template as L1template
, L1.title as L1title
, L2.ID as L2ID,
, L2.Template as L2template
, L2.title as L2title
, L3.ID as L3ID,
, L3.Template as L3template
, L3.title as L3title
from t_heirarchy H1
inner join t_contents L1
on H1.Child = L1.ID
inner join t_heirarchy H2
on H1.Child = H2.Parent
inner join t_contents L2
on H2.Child = L2.ID
inner join t_heirarchy H3
on H2.Child = H3.Parent
inner join t_contents L3
on H3.Child = L3.ID
where H1.Parent=0

note that the WHERE clause provides the starting point (i.e. all root nodes)

you'll need LEFT OUTER instead of INNER joins if any hierarchies don't go 3 levels deep

the separate t_heirarchy table is unusual, and the only advantage of having it separate is that you can place a child under more than one parent

if yours is a strict hierarchy, i.e. a top-down, one-to-many hierarchy, then i recommend doing away with the separate t_heirarchy table and allowing each entry in t_contents to reference its parent

your query would then have 3 tables in it instead of 6

rudy

 

Rudy,

Thanks for you help but I still can't get the query to work. I have changed the INNER JOINS to LEFT JOINS and removed the surplus commas in the SELECT but it doesn't work. I get a 'missing operator' error.

sql = "select L1.ID as L1ID
, L1.Template as L1template
, L1.title as L1title
, L2.ID as L2ID
, L2.Template as L2template
, L2.title as L2title
, L3.ID as L3ID
, L3.Template as L3template
, L3.title as L3title
from t_heirarchy H1
left join t_contents L1
on H1.Child = L1.ID
left join t_heirarchy H2
on H1.Child = H2.Parent
left join t_contents L2
on H2.Child = L2.ID
left join t_heirarchy H3
on H2.Child = H3.Parent
left join t_contents L3
on H3.Child = L3.ID
where H1.Parent= "& recordID &";"

We will have certain cildren under various parents so the current table structure must stay.

What I don't understand is how I can pass an article reference to the query? When the user clicks on the link, I need to SELECT the parents titles. I surely won't be able to do this if I am passing it's parent? I need to pass the ID of the current article (pulled from a REQUEST.QUERYSTING) to the query and get the parents from that.

Sorry if I'm being a pain, the query is really doing my head in. Thanks for your help.

 

[r937]

ah, i get it -- my apologies, i shoul dhave understood what you were doing

your WHERE clause is on a specific article, and you want to go "up" the hierarchy

the problem is, what if the article is more than three deep?

and what if, in ascending the tree, a child belongs to more than one parent? which breadcrump do you want to follow upwards?

 

[JeepStone]

Sorry Rudy, I've also provided you with some duff info. I'm not concerned with more than 3 levels in the site, so it doesn't matter where the article comes as long as I always show the top 3 parents. Also, a child will never have more than one parent. I misunderstood what you said in the previous post. JeepStone

http://www.eggsbox.co.uk

 

[r937]

if a child has at most one parent, then i suggest you incorporate the parent fk into the t_contents table -- you will really love the simplicity this will bring to your queries

as it stands, to go "up" three levels, you're right, use outer joins, since the article in question could be at the 1st or 2nd level

the WHERE clause will have a recordID for the article, and you simply follow the links, from content to hierarchy on matching child key (in your two-table structure)

rudy