?
Solved

Parent Child count

Posted on 2011-03-07
8
Medium Priority
?
380 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 2

Accepted Solution

by:
grossac earned 1000 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 1000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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 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.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

752 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