demo, Sql Server - rextester

Language: Layout:

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

WITH HouseholdMember AS (
    SELECT 100 AS MemberID, 200 AS HouseholdID, 1 AS PersonID UNION ALL
    SELECT 101, 200, 2 UNION ALL
    SELECT 101, 200, 2 UNION ALL
    SELECT 102, 200, 3 UNION ALL
    SELECT 103, 300, 4 UNION ALL
    SELECT 104, 300, 5 UNION ALL
    SELECT 105, 300, 6
),
Person AS (
    SELECT 1 AS PersonID, 'Josh' AS FirstName, 'Smith' AS LastName, 'Brown' AS HairColor UNION ALL
    SELECT 2, 'Jerry', 'Smith', 'Black' UNION ALL
    SELECT 3, 'Ethan', 'Smith', 'Red'   UNION ALL
    SELECT 4, 'Mike',  'Jones', 'Black' UNION ALL
    SELECT 5, 'Devan', 'Jones', 'Black' UNION ALL
    SELECT 6, 'Todd',  'Jones', 'Black'
),
Household AS (
    SELECT 200 AS HouseholdID, 'Smith''s' AS Name, '123 Candy Dr' AS Address UNION ALL
    SELECT 300, 'Jones''s', '812 Dentist Ln'
),
cte AS (
    SELECT hm.HouseholdID
    FROM HouseholdMember hm
    INNER JOIN Person p ON hm.PersonID = p.PersonID
    GROUP BY hm.HouseholdID
    HAVING COUNT(DISTINCT p.HairColor) > 1
)

SELECT *
FROM Household
WHERE HouseholdID IN (SELECT HouseholdID FROM cte);

View schema
Execution time: 0,06 sec, rows selected: 1, rows affected: 0, absolute service time: 4,22 sec