Yahoo style directory

Hi.

I'm trying to write a directory like Yahoo in ASP using an Access database. I have three tables:

Companies - Contains all information about each company including a number which represents which catagory it is in.

Catagory Listing - Listing of all catagories with a unique number next to each one.

Subcatagories - Two fields, first catagory ID and second catagory ID, to allow multiple catagories within catagories.

It all works fine apart from when I'm deep inside multiple subcatagories, I can't see a way of finding the full catagory position (i.e. Shopping > Car & Motoring Accessories > Car Electronics).

I probably haven't explained this very well, but if anyone thinks they can help, it would be much appreciated. Thanks.

Andy.

What do you mean exactly by:

I can't see a way of finding the full catagory position (i.e. Shopping > Car & Motoring Accessories > Car Electronics).

I can't display the full path, because the database will only return the current catagory, not how you got to it. I have since written a new table (Paths), two columns, 1st: each catagory number, 2nd: each path step. It seems to work but it's basically duplicating the data in the subcatagory table. I also thought about doing a loop to step back through the subcatagory table, but I'm not sure if this is possible in SQL?

I have attached the database, even though it has very little data in it, hopefully it will explain itself.

Thanks.

Andy.

"I can't display the full path, because the database will only return the current catagory"

Depending on how you join the tables, you can return any and all fields.

One thing that might help is to pass the id of each item the user clicks on. Ex:

Shopping -> Cars -> Radios

Each category above has a database id associated with it. Ex:

Shopping (Id 1) -> Cars (Id 5) -> Radios (Id 14)

When the user clicks on Shopping, pass Id to the receiving page. So now you have "1". When the user clicks on Cars, pass the id of shopping and cars(5). So now the receiving page has "1,5" When the user clicks on Radio, the query returns the id of Radios. So now you have "1,5,14". There is your path.

Does this makes sense? Hopefully I understand your question as well. Another thing, when joining records, you will get duplicates simply because of the nature of joins. But you can try using SELECT DISTINCT to only return distinct records.

Hope this helps.

Thanks for the reply.

Except the full path without using the new Path table, unless it is possible to do some sort of loop to step back through each catagory, but I don't like the sound of that much..

I preferbly wanted to avoid this method because of security. If someone starts manually changing the numbers it could put you in a valid catagory with an invalid path, which would cause alot of problems if they submit a site. I would still need to verify the full path somehow.

So far I don't think it's possible to get duplicate records, but I will keep it in mind for the future.

Andy.