Solved

Parent Child count

Posted on 2011-03-07
8
373 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Backup Your Microsoft Windows Server®

Backup 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.

Join & Write a Comment

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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now