Using query values in sub-query to get count data from another table

sql, sql-server, t-sql

Solution

Using subselects is one option but I prefer to `JOIN` both tables and use a `GROUP BY` clause using a `CASE`statement to get the totals.

SELECT b.description
       , COUNT(*) AS Items
       , SUM(CASE WHEN c.itemComplete = true THEN 1 ELSE 0 END) AS CompleteItems
FROM   tableB b
       LEFT OUTER JOIN tableC c ON c.childid = b.childid
WHERE  b.parentID = 'Y'
GROUP BY
       b.description

If you insist on using your original statement, all that was missing was the reference to the outer table `tableB.childID`

select description,  
(select count (*) from tableC where childId = tableB.childID) as Items,
(select count (*) from tableC where childId = tableB.childID And itemComplete = true) as CompleteItems
from tableB where parentId=Y

or reformatted

SELECT description
       ,  (SELECT COUNT(*) FROM tableC WHERE childId = tableB.childID) AS Items,
       ,  (SELECT COUNT(*) FROM tableC WHERE childId = tableB.childID AND itemComplete = true) AS CompleteItems
FROM   tableB 
WHERE  parentId=Y

Problem

I have a parent/child/grandchild type table relationship defined as follows: tableA: parentID description tableB: childID parentId description tableC: grandchildID childId description itemComplete I need to write a query that will list all records in tableB for any given parentID, along with the total count of grandchild records and the total count of grandchild records completed (where itemComplete = true). the parentID will be part of the where clause (select * from tableB where parentId = x). The problem I can't figure out is how to get the counts from tableC because the count is dependent on the current row value of the childId. In other words, I need some sort of query that looks like: ``` select description, (select count (*) from tableC where childId = X) as Items, (select count (*) from tableC where childId = X And itemComplete = true) as CompleteItems from tableB where parentId=Y ``` Where X is the current row's childId from tableB. How do I reference the childId from each row in my sub queries to get the number of items and number of items complete?

Original source