How to get this relation(Need Sql Query)?

Hi friends

I have one Table called tblCategory.

I have three Column CatID, CatName, ParentID

I have many records in this Table

CatID CatName ParentID

1 Cat1 0

2 Cat2 1

3 Cat3 1

4 Cat4 2

5 Cat5 2

6 Cat6 0

7 Cat7 6

8 Cat8 6

9 Cat9 7

10 Cat10 7

11 Cat11 8

Here I have Main Category which has ParentID 0 [ Cat1 and Cat6 ]

I Have Sub categories of Cat1 Which has ParentID 1(CatID 1 of Cat1) [ Cat2 and Cat 3 ]

Cat 2 has also sub category with ParentID 2 (CatID 2 of Cat2) [ Cat4 and Cat5]

I want result looks like as

Cat1 Cat6 ->>>>>ParentID 0
- Cat2 -Cat7 ->>>>>ParentID 1(CatID of Root Cat1) and ParentID 6(CatID of Root Cat6)
- - Cat4 - -Cat9 ->>>>>PaerntID 2(CatID of Root Cat2) and ParentID 7(CatID of Root Cat7)
- - Cat5 - -Cat10 ->>>>>PaerntID 2(CatID of Root Cat2) and ParentID 7(CatID of Root Cat7)
- Cat3 -Cat8 -->>>>>ParentID 1(CatID of Root Cat1) and ParentID 6(CatID of Root Cat6)
--No record --Cat11

Can anybody give me solution?

Thanks

See if this article on working out JOIN syntax will help:http://www.mikesdotnetting.com/Article.aspx?ArticleID=72

|||

There are two tables but in my case I have only one table.

|||

impathan:

There are two tables but in my case I have only one table.

So you have. Sorry, I saw the word Relation in the title of your post and thought you were after somerthing else.

Is what you are after something like the threaded view of a discussion board or similar? If so, one solution might be a recursive function. If not, could you explain the logic beind the diagram you have supplied? I can't, for example see the basis on which Cat3 and Cat8 on the penultimate line are related.

|||

impathan:

There are two tables but in my case I have only one table.

You can query a table twice in the same statement and join it to itself. :) You just need to give each usage of the table a different table alias.