0

Table 1:

ID (unqiue), Name, Address

Table 2:

RecordId, ID (key of table 1),  Child name

In one query, I want to retrieve all rows of Table 1 with one additional column which will be the count of all record in table 2 from ID (that is number of children for each ID in table 1). Can't figure out how to format a query to retrieve this data.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Allen King
  • 2,372
  • 4
  • 34
  • 52

3 Answers3

2

Simply Join and apply count

select T1.*, COUNT(T2.RECORDID)AS T2COUNT from Table1 T1
INNER JOIN TABLE2 T2 ON T1.ID= T2.ID
--LEFT JOIN  TABLE2 T2 ON T1.ID= T2.ID  --if you need 0 child records (from commets by @Cha)
GROUP BY T1.ID  , T1.Name, T1.Address
Shakeer Mirza
  • 5,054
  • 2
  • 18
  • 41
1

The correct way of doing this will be with a OUTER JOIN:

SELECT a.ID, a.Name, a.Address, b.cnt
FROM Table1 a
LEFT OUTER JOIN
(SELECT ID, count(*) cnt from Table2 GROUP BY ID) b
ON a.ID = b.ID

The incorrect way will be with a help of a correlated sub-query:

SELECT a.ID, a.Name, a.Address, 
(SELECT count(*) FROM Table2 b WHERE b.ID = a.ID) as cnt
FROM Table1 a

Here is a discussion about correlated subqueries vs OUTER JOINs, if you are interested

Community
  • 1
  • 1
cha
  • 10,301
  • 1
  • 18
  • 26
  • And why Left Join a Set? – Shakeer Mirza Jan 19 '17 at 04:46
  • @ShakeerMirza: to select people without children? – cha Jan 19 '17 at 04:46
  • @ShakeerMirza: I feel that grouping by `ID, Name, Address` like you did works in this particular case until you start adding more tables. You add another table to your query and suddenly realise that you need to add more columns to the GROUP BY clause. In my opinion it is excessive – cha Jan 19 '17 at 04:51
  • Yeah came to know the intention of Sub set. Upvoted for the same already. – Shakeer Mirza Jan 19 '17 at 04:53
  • Also, if you need to add more columns to the T1 (like email, address, phone etc) they need to be repeated in the SELECT clause and in the GROUP BY clause – cha Jan 19 '17 at 04:55
  • The real scenario was a bit more involved with about 10 tables involved. I found "the incorrect way" above more straight forward for me to understand and it seems to work well. Thanks. – Allen King Jan 19 '17 at 05:41
0

Group by table1 fields and count total records in table2: here T1 alias of table1 and T2 alias of table2.

select T1.ID, T1.Name, T1.Address, count(T2.ID) as total_records
from table1 as T1
left outer join table2 as T2 on T2.ID=T1.ID
group by T1.ID, T1.Name, T1.Address
Vikram Jain
  • 5,498
  • 1
  • 19
  • 31