Query with Self Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query with Self Join

Author

Topic

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-22 : 01:20:14

Hi , I have a table items with itemid,itemname,qty,unit, pitemid

Now I am able to get the value of itemname and parent item name with

select c.iname as cname,p.iname as pname
from item p
join item c on p.itemid=c.pitemid

now i want get the in the form

Parentitem1
Childitem11
Childitem12
Parentitem2
Childitem21
Childitem22

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-22 : 01:35:22

This looks more like a presentation issue.

Just use order by to return the result in the order you want and display it in your front end application

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-22 : 02:14:51

I want to get this using a SQL Query using self join

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 02:35:40

This should do the trick

select		i1.iname
from		item i1
left join	item i2 on i2.itemid = i1.pitemid
order by	isnull(i2.iname, i1.iname),
 		i2.iname

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 02:42:23

Oh, here is the test data

insert	@item
select	0 itemid, 'Parentitem1' iname, null pitemid union all
select	1, 'Parentitem2', null union all
select	2, 'Childitem12', 0 union all
select	3, 'Childitem21', 1 union all
select	4, 'Childitem11', 0 union all
select	5, 'Childitem22', 1

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources