SELECT case ( SELECT Count(*) FROM A2 WHERE ID = A1.ID) WHEN 1 THEN 'A' ELSE 'B' END) AS Name, (SELECT Count(*) FROM A2 WHERE ID = A1.ID) AS Code FROM A1
SELECT (case ( SELECT Count(*) FROM A2 WHERE ID = A1.ID) WHEN 1 THEN 'A' ELSE 'B' END) AS Name, (SELECT Count(*) FROM A2 WHERE ID = A1.ID) AS Code FROM A1
,Code
FROM (
SELECT ID
,(SELECT Count(*) FROM A2 WHERE ID = A1.ID) 'Code'
FROM A1
)t
Name = (CASE t.Code WHEN 1 THEN 'A' ELSE 'B' END)
from
A1
inner join
(SELECT Code=Count(*) FROM A2 group by ID) t
on
A1.ID = t.ID
from A1 a left join A2 b on a.id=b.id
group by a.id
FROM (
SELECT ID,(SELECT Count(*) FROM A2 WHERE ID = A1.ID) 'Code' FROM A1
) t