how to sperate the category and its sub category?

Question

2.50/5 (2 votes)

See more:

this is my table structure..

id name parentid
1 Mobile null
2 Nokia 1
3 nokia lumia 2
4 nokia Lumia520 3
5 computer null
6 dell 5
7 lenovo 5
8 dell 697 6
9 lenovo 220 7

now if i select mobile I want output like

mobile
nokia
nokia Lumia
nokia Lumia520

is it possible?

Posted 11-Mar-14 3:04am

utm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

King Fisher · Answer 1 · 2014-03-11T03:21:00

Solution 2

use this:

SQL

with cte(parent_id,id)
as
(
select parent_id,Id from table where parent_id is null
union all

select parent_id,ID from table as a inner join cte as b on b.id=a.parent_id
)
select *from cte

Posted 11-Mar-14 3:21am

King Fisher

Comments

Ankur^\m/ 11-Mar-14 9:24am

I left that for the user to understand and try himself/herself.

Tom Marvolo Riddle 11-Mar-14 9:29am

I agree with you.+5 for that

Ankur^\m/ 11-Mar-14 9:31am

Thank you!

King Fisher 11-Mar-14 9:32am

i hope he will learn from your link .i thought he stuck with that. :)

Ankur^\m/ · Answer 2 · 2014-03-11T03:13:00

Solution 1

Yes, it is possible. You will need to use Common Table Expressions (CTE)[^] here. For more details see the MS Technet article - Recursive Queries Using Common Table Expressions
[^]

Posted 11-Mar-14 3:13am

Ankur^\m/

Comments

utm 11-Mar-14 10:37am

thanks..
i never heard about this..
i got new things to learn..