Trying to filter a list with subdirectories

[planetdrouin]

I have a table which contains a list of categories which looks like:

Category ID Category Name
11 Cash
111 Petty Cash
12 Bank Account
121 Citibank
122 Wells Fargo
21 Utilities
211 Telephone
2111 SBC
2111 AT&T
etc...

I would like to be able to retrieve a list from the above table which only selects the lines which have no subdirectories. For instance, I want to filter out line 11, as it is the total of its subdirectories, which in this case is simply line 111.

Has anyone encountered this before or know how to write a function which would do this?

Lawrence

 

[cmmrfrds]

How do you determine the difference between a directory and a sub directory?

 

[planetdrouin]

Thanks for the quick reply. Currently I don't have any direct method of determining a difference between a directory and a sub directory. However, by definition, if a category is a directory, then no category with the same numerical prefix but with more numbers will exist. For instance, category number '21' is a directory if no category exists that has a number such as '211'. So I was hoping to figure out a way to use code (loop and if functions) to look to see if a category has a number which no other category has as a prefix, and if so keep it, otherwise filter out.

The categories are provided by a different accounting program.

Any other sugestions?

Lawrence

 

[cmmrfrds]

Try a query like this.

SELECT A.CategoryID AS id, A.CategorName AS cname, dt.cnt
FROM Categorys AS A INNER JOIN [Select count(*) as cnt, Left(B.categoryID,2) as cat From Categorys as B
Group by Left(B.categoryID,2) ]. AS dt ON dt.cat = A.categoryID
WHERE dt.cnt < 2;