how to select category - 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 2005 Forums

Transact-SQL (2005)

how to select category

Author

Topic

sks198117
Starting Member

46 Posts

Posted - 2008-01-09 : 01:31:14

i've following table

catid parentid catname
1 0 A
2 0 B
3 1 A1
4 2 B1
5 0 C

0 for parent category

iwant to select those parent category which has subcat otherwise not.

can anyone help me to write tsql?
Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-09 : 02:00:02

SELECT * FROM table where parentid <> 0

sks198117
Starting Member

46 Posts

Posted - 2008-01-09 : 02:05:15

quote:
Originally posted by visakh16

SELECT * FROM table where parentid <> 0

I want to select those parent category which has subcategory only

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-09 : 03:21:14

Try this:-

set nocount on
DECLARE @Temp Table
(
catid int,
parentid int,
catname varchar(2)

)

INSERT INTO @Temp Values (1 ,0 ,'A')
INSERT INTO @Temp Values (2, 0 ,'B')
INSERT INTO @Temp Values (3, 1, 'A1')
INSERT INTO @Temp Values (4, 2, 'B1')
INSERT INTO @Temp Values (5, 0 ,'C')

SELECT t1.catid,t1.catname as 'ParentName',t2.catid as 'ChildID',t2.catname as 'ChildName'
FROM @Temp t1
INNER JOIN @Temp t2
ON t2.parentid=t1.catid

output :-

catid ParentName ChildID ChildName
----------- ---------- ----------- ---------
1 A 3 A1
2 B 4 B1

sks198117
Starting Member

46 Posts

Posted - 2008-01-09 : 03:35:29

Thanks visakh16 that's solved my problem.
Thanks again

quote:
Originally posted by visakh16

Try this:-

set nocount on
DECLARE @Temp Table
(
catid int,
parentid int,
catname varchar(2)

)

INSERT INTO @Temp Values (1 ,0 ,'A')
INSERT INTO @Temp Values (2, 0 ,'B')
INSERT INTO @Temp Values (3, 1, 'A1')
INSERT INTO @Temp Values (4, 2, 'B1')
INSERT INTO @Temp Values (5, 0 ,'C')

SELECT t1.catid,t1.catname as 'ParentName',t2.catid as 'ChildID',t2.catname as 'ChildName'
FROM @Temp t1
INNER JOIN @Temp t2
ON t2.parentid=t1.catid

output :-

catid ParentName ChildID ChildName
----------- ---------- ----------- ---------
1 A 3 A1
2 B 4 B1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources