맞는지 몰겠네요. 그냥 참고하세요.

DECLARE @TABLE1 TABLE
(
 GroupCode VARCHAR(1),
 GroupName VARCHAR(20)
)

DECLARE @TABLE2 TABLE
(
 GroupCode VARCHAR(1),
 ModelCode VARCHAR(1),
 ModelName VARCHAR(20)
)

DECLARE @TABLE3 TABLE
(
 GoupCode VARCHAR(1),
 ModelCode VARCHAR(1),
 SN VARCHAR(4)
)

INSERT INTO @TABLE1 VALUES('1', '그룹1')
INSERT INTO @TABLE1 VALUES('2', '그룹2')
INSERT INTO @TABLE1 VALUES('3', '그룹3')

INSERT INTO @TABLE2 VALUES('1', '1', '모델1')
INSERT INTO @TABLE2 VALUES('1', '2', '모델2')
INSERT INTO @TABLE2 VALUES('2', '1', '모델3')
INSERT INTO @TABLE2 VALUES('2', '2', '모델4')
INSERT INTO @TABLE2 VALUES('3', '1', '모델5')
INSERT INTO @TABLE2 VALUES('3', '2', '모델6')

INSERT INTO @TABLE3 VALUES('1', '1', '1101')
INSERT INTO @TABLE3 VALUES('1', '1', '1102')
INSERT INTO @TABLE3 VALUES('1', '2', '1201')
INSERT INTO @TABLE3 VALUES('2', '1', '2101')
INSERT INTO @TABLE3 VALUES('2', '1', '2102')
INSERT INTO @TABLE3 VALUES('3', '1', '3101')
INSERT INTO @TABLE3 VALUES('3', '1', '3102')
INSERT INTO @TABLE3 VALUES('3', '1', '3103')
INSERT INTO @TABLE3 VALUES('3', '1', '3104')

SELECT
 A.GroupCode, B.ModelCode, A.GroupName, B.ModelName, COUNT(C.ModelCode)
FROM 
 @TABLE1 A
 INNER JOIN
 @TABLE2 B
 ON (A.GroupCode=B.GroupCode)
 LEFT JOIN
 @TABLE3 C
 ON (B.GroupCode=C.GoupCode AND B.ModelCode=C.ModelCode)
GROUP BY
 A.GroupCode, A.GroupName, B.ModelCode, B.ModelName

아...테이블은 다 만들어놨었구요 ^^ 암튼 감사

감사 감사~