Solved

Parent Child count

Posted on 2011-03-07
8
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 65
sql2016-WIn10: standard,for SQL servc-account.. 51 53
Update one rows based on previous row 5 35
debug user defined function in visual studio 2015 2 50
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

737 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