CODEWITHSUNDEEP
sqlsql-server
Allen King
Allen King

Reputation: 2516

SQL Query, how to get data from two tables

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.

Upvotes: 0

Views: 76

Answers (3)

Vikram Jain
Vikram Jain

Reputation: 5588

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

Upvotes: 0

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

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

Upvotes: 2

cha
cha

Reputation: 10411

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

Upvotes: 1

Related Questions