Solved

Parent Child count

Posted on 2011-03-07
8
377 Views
Last Modified: 2012-05-11
I need to count the number of rows in the child table, this is a simple question, but i am stuck trying to write a query.

0
Comment
Question by:byte1
8 Comments
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 35058497
Hi,

SELECT parentID, count(*)
FROM table
GROUP BY parentID

If you need something more specific, post more info.


cheers
0
 

Author Comment

by:byte1
ID: 35058582
Parent Table:

ID   EmployeeName
10  aa
11  bb
12  cc
13  dd

Child Table:
ChildID   ID          Cases
01          11           '133333''
02          11           'sdfdsfd'
03          13            ''
04          14            ''  
05          11            ''
06           11           ''


I need 2 things,

1) those with No Comments
2) those with Two Comments

I just need the ID .. the table is rather huge with at least 50K rows of data. I am looking at an efficiency and speed of the query..
0
 

Author Comment

by:byte1
ID: 35058672
Sorry the Cases column is  Comments ..
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 2

Accepted Solution

by:
grossac earned 250 total points
ID: 35058836
SELECT ID
FROM ChildTable
GROUP BY
ID
HAVING COUNT(*) = 2

SELECT ID
FROM ChildTable
WHERE comments = ''
GROUP BY
ID
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 35058861
Hi,

when you say 2 comments, do you mean 2 rows, or 2 rows with value in the column 'comments' ??

for your previous sample, what would be your output??


cheers
0
 

Author Comment

by:byte1
ID: 35058896
2 rows with value in the column 'Comments'
0
 
LVL 8

Assisted Solution

by:raulggonzalez
raulggonzalez earned 250 total points
ID: 35059006
Hi, try this...


SELECT ID
FROM Childtable
WHERE comments <> ''
HAVING COUNT(*) = 2

UNION ALL

SELECT ID
FROM childtable
WHERE comments = ''
HAVING COUNT(*) = 1
0
 
LVL 5

Expert Comment

by:jijeesh
ID: 35059061

select p.ID, 2 as count
 from Parent p
 join Child c on p.ID = c.ParentID
group by p.ID having count(0) = 2
UNION ALL
select p.ID, 0 as count
 from Parent p
left join Child c on p.ID = c.ParentID
where c.parentid is null
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql how to count case when 4 29
SQL trigger 5 32
sql 2016 data tools breakdown.. 1 25
SQL QUERY 3 33
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

685 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