Hi I am using the below query to satisfied the below conditions.

There are two conditions used for SEC_MORTGAGOR_SS_NUMBER and SEC_CO_MORTGAGOR_SS_NUMBER one for web enabled first two and last two for SSN mismatch.

the below query giving me the results for both SEC_MORTGAGOR_SS_NUMBER and SEC_CO_MORTGAGOR_SS_NUMBER if both web enbaled and SSN mismatch satisfied,however I also need the below conditions

1.if only SEC_MORTGAGOR_SS_NUMBER satisfied the web enabled and SSN mismatch then need only this in output file.
2.if only SEC_co_MORTGAGOR_SS_NUMBER satisfied the web enabled and SSN mismatch then need only this in output file.
2.if SEC_CO_MORTGAGOR_SS_NUMBER doesn't exist then only SEC_MORTGAGOR_SS_NUMBER will go to file.

The above conditions should be satisfied with table MSP_FILE used in query

Can anyone help me in below query




SELECT
LOAN_NUMBER,
MORTGAGOR_FIRST_NAME,
MORTGAGOR_MIDDLE_NAME,
MORTGAGOR_LAST_NAME,
SEC_MORTGAGOR_SS_NUMBER,
CO_MORTGAGOR_FIRST_NAME,
CO_MORTGAGOR_MIDDLE_NAME,
CO_MORTGAGOR_LAST_NAME,
SEC_CO_MORTGAGOR_SS_NUMBER,
BILLING_ADDRESS_LINE_3,
BILLING_ADDRESS_LINE_4,
BILLING_CITY_NAME,
BILLING_STATE,
BILLING_ZIP_CODE,
IREG_BORROWER_REGISTERED_FLAG,
IREG_COBRROWR_REGISTERED_FLAG
FROM MSP_FILE A
WHERE A.SEC_MORTGAGOR_SS_NUMBER NOT EXISTS (
SELECT X.TAXPAYER_ID
FROM tn.TBCUSTMR X INNER JOIN TN.TBCUACAD B
ON X.ID_NUMBER = B.FK_TBCUSTMRID_NUMB
INNER JOIN CI.TBACCNT C
ON B.FK_TBACCNTUNIQUE_I = C.UNIQUE_ID_NUMBER
WHERE C.PROD_TYPE = 'WEB')
and A.SEC_CO_MORTGAGOR_SS_NUMBER NOT EXISTS (
SELECT X.TAXPAYER_ID
FROM TN.TBCUSTMR X INNER JOIN TN.TBCUACAD B
ON X.ID_NUMBER = B.FK_TBCUSTMRID_NUMB
INNER JOIN CI.TBACCNT C
ON B.FK_TBACCNTUNIQUE_I = C.UNIQUE_ID_NUMBER
WHERE C.PROD_TYPE = 'WEB')
AND A.SEC_MORTGAGOR_SS_NUMBER NOT EXISTS (
SELECT DISTINCT SSN
FROM (
SELECT CIS_TIN_1 AS SSN
FROM TIN_MISMATCH
WHERE RESOLVE_DT IS NULL
UNION
SELECT CIS_TIN_2 AS SSN
FROM TIN_MISMATCH
WHERE RESOLVE_DT IS NULL
UNION
SELECT MTG_TIN_1 AS SSN
FROM TIN_MISMATCH
WHERE RESOLVE_DT IS NULL
UNION
SELECT MTG_TIN_2 AS SSN
FROM TIN_MISMATCH
WHERE RESOLVE_DT IS NULL))
AND A.SEC_CO_MORTGAGOR_SS_NUMBER NOT EXISTS (
SELECT DISTINCT SSN
FROM (
SELECT CIS_TIN_1 AS SSN
FROM TIN_MISMATCH
WHERE RESOLVE_DT IS NULL
UNION
SELECT CIS_TIN_2 AS SSN
FROM TIN_MISMATCH
WHERE RESOLVE_DT IS NULL
UNION
SELECT MTG_TIN_1 AS SSN
FROM TIN_MISMATCH
WHERE RESOLVE_DT IS NULL
UNION
SELECT MTG_TIN_2 AS SSN
FROM TIN_MISMATCH
WHERE RESOLVE_DT IS NULL));