t, Sql Server - rextester

Language: Layout:

CREATE TABLE TableA
    ([Member_Key] int, [VisitCount] int, [date] datetime)
;
    
INSERT INTO TableA
    ([Member_Key], [VisitCount], [date])
VALUES
    (4000, 1, '2014-05-07 00:00:00'),
    (4000, 1, '2014-05-09 00:00:00'),
    (4001, 2, '2014-05-08 00:00:00')
;

CREATE TABLE TableB
    ([Member_Key] int, [Condition] varchar(12))
;
    
INSERT INTO TableB
    ([Member_Key], [Condition])
VALUES
    (4000, 'Diabetes'),
    (4000, 'Diabetes'),
    (4001, 'Hypertension')
;

CREATE TABLE TableC
    ([Member_Key] int, [Member_Gender] varchar(1), [Member_DOB] datetime)
;
    
INSERT INTO TableC
    ([Member_Key], [Member_Gender], [Member_DOB])
VALUES
    (4000, 'M', '1970-05-21 00:00:00'),
    (4001, 'F', '1968-02-19 00:00:00')
;

SELECT [Condition], 
       [Member_Gender] as [Gender], 
       SUM([VisitCount]) as [VisitCount]
FROM TableA A
JOIN (SELECT DISTINCT [Member_Key], [Condition] 
      FROM TableB) B 
  ON A.[Member_Key] = B.[Member_Key]
JOIN TableC C 
  ON A.[Member_Key] = C.[Member_Key]
WHERE DATEDIFF(year, [Member_DOB], GETDATE()) BETWEEN 45 AND 50
GROUP BY [Condition], [Member_Gender]

View schema
Execution time: 0,05 sec, rows selected: 2, rows affected: 8, absolute service time: 0,19 sec