Solved

Parent Child count

Posted on 2011-03-07
8
379 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

696 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