Select multiple count(*) in multiple tables with single query

Question

I have 3 tables:

1. Basic

id	name	description
2	Name1	description2
3	Name2	description3

2. LinkA

id	linkA_ID
2	344
3	3221
2	6642
3	2312
2	323

3. LinkB

id	linkB_ID
2	8287
3	42466
2	616422
3	531
2	2555
2	8592
3	1122
2	33345

I want to get results as the table below:

id	name	description	linkA_count	linkB_count
2	Name1	description2	3	2
3	Name2	description3	5	3

my query:

SELECT
     a.id
    ,a.name
    ,a.description
    ,COUNT(b.linkA_ID) AS linkA_count
    ,COUNT(c.linkB_ID) AS linkb_count
FROM
    basic a 
    JOIN linkA b on (a.id = b.id)
    JOIN linkb c on (a.id = c.id)
GROUP BY
    a.id
    ,a.name
    ,a.description

Result from the query is count of linkA always same as linkB

Answer 1

Try This (using SubQuery)

SELECT
 basic.id
,basic.name
,basic.description
,(select Count(linkA_ID) from LinkA  where LinkA.id=basic.id) as LinkACount
,(select Count(linkB_ID) from LinkB  where LinkB.id=basic.id) as LinkBCount FROM basic