StackOverflow question #55294680, Sql Server

Language: Layout:

CREATE TABLE Table_LKP_AlertMastInfo (DistrictID INT, FieldName VARCHAR(255), AlertOptionValue VARCHAR(255));
    INSERT INTO Table_LKP_AlertMastInfo (DistrictID, FieldName, AlertOptionValue) VALUES (71, 'AreYouMarried', 'Yes');
    INSERT INTO Table_LKP_AlertMastInfo (DistrictID, FieldName, AlertOptionValue) VALUES (71, 'Gender', 'Female');
    INSERT INTO Table_LKP_AlertMastInfo (DistrictID, FieldName, AlertOptionValue) VALUES (72, 'AreYouMarried', 'Yes');

CREATE TABLE Table_RegistrationInfo (CandidateId VARCHAR(255), DistrictID INT, AreYouMarried VARCHAR(255), Gender VARCHAR(255));
    INSERT INTO Table_RegistrationInfo (CandidateId, DistrictID, AreYouMarried, Gender) VALUES ('Can001', 71, 'Yes', 'Female');
    INSERT INTO Table_RegistrationInfo (CandidateId, DistrictID, AreYouMarried, Gender) VALUES ('Can002', 71, 'No', 'Female');
    INSERT INTO Table_RegistrationInfo (CandidateId, DistrictID, AreYouMarried, Gender) VALUES ('Can003', 72, 'Yes', 'Man');
    INSERT INTO Table_RegistrationInfo (CandidateId, DistrictID, AreYouMarried, Gender) VALUES ('Can004', 72, 'No', 'Man');

SELECT subq.*
    FROM
    (SELECT CandidateId,
            (SELECT COUNT(*)
             FROM Table_LKP_AlertMastInfo ami
             WHERE ami.DistrictID = ri.DistrictID
               AND ami.FieldName ='AreYouMarried'
               AND ami.AlertOptionValue = ri.AreYouMarried) +
            (SELECT COUNT(*)
             FROM Table_LKP_AlertMastInfo ami
             WHERE ami.DistrictID = ri.DistrictID
               AND ami.FieldName ='Gender'
               AND ami.AlertOptionValue = ri.Gender) AS [count]
     FROM Table_RegistrationInfo ri) subq
    WHERE subq.[count] > 0;

View schema
Execution time: 0,03 sec, rows selected: 3, rows affected: 7, absolute service time: 0,22 sec, absolute service time: 0,22 sec