Sql help, parent child self join

I'm trying to display a list of expenses in a drop down list.
I need to return a row for each like this. Is this possible with my current setup? See screen shot
Shipping Supplies
Shipping Supplies - Boxes
Shipping Supplies - Bubble Wrap
Shipping Supplies - Tape
expense.jpg
LVL 8
JRockFLAsked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
you can try without the cast, but I believe it will create a runtime error if you remove it. I'm using it to make sure that the new column created is of a specific size.
Level is just a pointer I'm using there, to see what level in your hierarchy you're. You don't really need in your example, but you could use is in the future.
0
 
ralmadaCommented:
If you're in SQL 2005 or above, try this:
;with CTE as (
	select	cast(Name as varchar(1000)) as Name,
		ExpenseTypeID,
		parent,
		0 as level
	from table1
	where parent is null
	union all
	select 	cast(d.Name + ' - ' + a.Name as varchar(1000)),
		a.ExpenseTypeID,
		a.Parent,
		d.level + 1
	from Table1 a
	inner join CTE d on d.ExpenseTypeID = a.Parent
)
select Name
from CTE

Open in new window

0
 
JRockFLAuthor Commented:
Thank you for your reply.
I will try out your sql, this is what I came up with...

SELECT a.Name As Cateogy
FROM ExpenseTypes a
WHERE a.Parent IS NULL
UNION
SELECT
      (SELECT Name FROM ExpenseTypes WHERE ExpenseTypeID = a.Parent) + ' ' + a.Name As Category
From ExpenseTypes a
INNER JOIN ExpenseTypes b
ON a.ExpenseTypeID = b.ExpenseTypeID
WHERE a.Parent IS NOT NULL
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
JRockFLAuthor Commented:
Yep yours works nicely, I'm on 2008.
What is that "with" ?
0
 
ralmadaCommented:
with is part of the Common Table Expressions (CTE) in SQL server 2005+, which allows among other things the possibility of "recursive" queries. This is ideal for creating hierarchies like yours.
See below for more details:
http://msdn.microsoft.com/en-us/library/ms190766.aspx 
 
0
 
JRockFLAuthor Commented:
The column name is already varchar, so the cast is not needed, correct?
Also, what does that level do?
0
 
k_murli_krishnaCommented:
SELECT c.ExpenseTypeID, c.Name, c.Description, p.Parent
FROM Table1 c, Table1 p
WHERE c.ExpenseTypeID = p.ExpenseTypeID;
0
 
JRockFLAuthor Commented:
OK great thank you. Level was displaying in all blue so I figured it was some required reserved word.
Thank you, I learned a lot.
0
All Courses

From novice to tech pro — start learning today.