Checking for value in select?
-
I have a hierarchical table: Example:
ID ParentID Name
1 null A
2 1 B
3 1 C
4 2 BA
and so on.
I want result like this:
ID Name
1 A has children
2 B has children
3 C
4 BA
Otherwise, a simple select statement which checks, if there are subcategories or inner categories within a category, if it is there, appends some constant string to the name else just returns the name.
Thanks in advance for all your help.
-
Look around before posting:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=219897
_____________
Code for TallyGenerator -
You can achieve this using a derived table.
select t1.id , t1.name , t2.childcount from mytable as t1 left join ( select parentid as id , count( id ) as childcount from mytable where ParentID IS NOT NULL group by ParentID ) as t2 ON t1.id = t2.id
--------------------
Colt 45 - the original point and click interface
-
Thanks for all your help. I didn't want the number of subcategories, but if the subcategories existed, the categoryname should have appended with something else, if not then just the categoryname.
Anyway the link Sergiy gave me the idea to use case. Just don't ask why I did not think it before.
-
So just take the field out of the select, or concatenate it with the name, or use a case, or ...
--------------------
Colt 45 - the original point and click interface -
Try this ...
SELECT
t1.id,
t1.name [Child],
t2.name [Parent]
FROM
category t1,
category t2
WHERE
t1.parentid = t2.id
Sathish
S A T ...
Sathish's Blog -
Try this:
If Object_id('test') is not null Drop Table Test
Create Table Test(Id int,ParentId int,[Name] varchar(256))
Insert Test Values(1,Null,'A')
Insert Test Values(2,1,'B')
Insert Test Values(3,1,'C')
Insert Test Values(4,2,'BA')
select
Id,
Name+Case IsNull((select top 1 t2.ParentId from test t2 where t2.ParentId=t1.Id),'')
when '' then ''
else ' Has children'
End[Status]
From Test t1
If Object_id('test') is not null Drop Table Test
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply