?
Solved

Make recordlist with column "has children" for each record

Posted on 2007-08-12
8
Medium Priority
?
230 Views
Last Modified: 2013-11-05
I have a recordlist that return all records with ParentId=@ParentId. In some way I need to know if each record in this result has any child records. Is there any way to make a column named "hasChildren" that returns True or False if the record has children (any records with ParentId = Id)?

Here is my Sorted Procedure:

ALTER PROCEDURE [dbo].[spGetArticles]
(
      @ParentId int
)
AS
BEGIN
      SET NOCOUNT ON;

      SELECT Id, ParentId, [Name] FROM tblArticle WHERE ParentId=@ParentId ORDER BY [Level],Id

END
0
Comment
Question by:webressurs
7 Comments
 
LVL 28

Accepted Solution

by:
imran_fast earned 1000 total points
ID: 19678991
ALTER PROCEDURE [dbo].[spGetArticles]
(
      @ParentId int
)
AS
BEGIN
      SET NOCOUNT ON;

      SELECT a.Id, a.ParentId, a.Name , case when (select count(1) from tblArticle B where b.parentid = a.id ) >0 then 'True' else 'False' end Haschild

FROM tblArticle a WHERE a.ParentId=@ParentId ORDER BY a.Level,a.Id

END
0
 
LVL 1

Author Comment

by:webressurs
ID: 19679030
Thank you imran fast!!
It worked, but it seems a bit "slow"... will the performance be good by doing it this way?
0
 
LVL 18

Assisted Solution

by:Yveau
Yveau earned 1000 total points
ID: 19680189
Webressurs,

Try this variant:
ALTER PROCEDURE [dbo].[spGetArticles]
(
      @ParentId int
)
AS
BEGIN
      SET NOCOUNT ON

      declare @HasChild varchar(5)
      if exists (select 1 from tblArticle where id = @ParentId)
      begin
            set @HasChild = 'True'
      end
      else
      begin
            set @HasChild = 'False'
      end

      SELECT Id, ParentId, [Name], @HasChild as HasChild FROM tblArticle WHERE ParentId=@ParentId ORDER BY [Level],Id

END

It might even perform better with a index (either clustered or non-clustered) on the id column
The performance should be better, because when you do a 'if exsist (select ...)' the statement will return  a positive return code as soon as it finds the first hit. A 'select count(1) ...' however will keep digging into the table until it has read all records to return the proper number of records that meet the condition. But in your case, you're not interested in whether you have 1 or 12 child records, you whether there is one or none. So in the worst case (no child records, or the last record is the only match) the performance will be the same (complete table scan) but in all other cases the 'if exists ...' approach will stop scanning at the first record that meets the criteria.

Good luck
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 28

Expert Comment

by:imran_fast
ID: 19682543
Try this it will be faster and also check if you have index on parentid and id columns

ALTER PROCEDURE [dbo].[spGetArticles]
(
      @ParentId int
)
AS
BEGIN
      SET NOCOUNT ON;

     
SELECT
      a.Id,
      a.ParentId,
      a.Name ,
      case when b.parentid is not null then 'True' else 'False' end Haschild
      
      FROM tblArticle a
      left outer join tblArticle B on b.parentid = a.id  
      
      WHERE a.ParentId=@ParentId

      ORDER BY
      a.Level,
      a.Id

END
0
 
LVL 18

Expert Comment

by:Yveau
ID: 19907639
Is there anything we can do for you regarding this Q ?
If you are satisfied with the answer, would please close the Q.

0
 
LVL 18

Expert Comment

by:Yveau
ID: 20211591
mbizup,

no problems with your recommendation.

Yveau
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20238191
Forced accept.

Computer101
EE Admin
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question