Sql-server – How to check values for records from different rows

I guess that what you would like is not exactly what you are asking for... so, correct me if I missinterpreted you.

Assuming this is your data (I represent it with a table, but it can be a view):

 CREATE TABLE v
 (
     ID varchar(9), 
     LETTER_ID int, 
     MEDICAL_ID int, 
     SICKNESS_ID int, 
     PRO_ID int, 
     EDU_ID int,
     TRAINING_ID int, 
     RECORD_ID int, 
     NID int
 ) ;

 INSERT INTO v
     (ID,         LETTER_ID, MEDICAL_ID, SICKNESS_ID, PRO_ID, EDU_ID, TRAINING_ID, RECORD_ID, NID)
 VALUES
     ('MI1390454', NULL,     NULL, NULL, NULL, NULL, 47584, NULL, NULL),
     ('MI1390454', NULL,     NULL, NULL, 4673735, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, 105419, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', 299080,   NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659652, NULL, NULL, NULL),
     ('MI1390033', NULL,     309609, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659650, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659654, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, 161077),
     ('MI1390033', NULL,     309608, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659651, NULL, NULL, NULL),
     ('MI1390454', NULL,     NULL, NULL, NULL, NULL, NULL, 154745, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659653, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, 94272, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, 44917, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, 105418, NULL, NULL, NULL, NULL)
 ;

What you actually want is to flatten these data first.

That is, GROUP all rows with the same ID, and take the only non null value of the other columns (or, if there are more than one, the one value that actually is of interest to you).

I have chosen to do this using the MAX aggregate function. You may want another one, such as MIN, or a MIN || MAX or something like a string_agg or group_concat (which MS-SQL Server doesn't yet provide):

 SELECT
     ID, 
     max(LETTER_ID) AS LETTER_ID,
     max(MEDICAL_ID) AS MEDICAL_ID,
     max(SICKNESS_ID) AS SICKNESS_ID, 
     max(PRO_ID) AS PRO_ID, 
     max(EDU_ID) AS EDU_ID, 
     max(TRAINING_ID) AS TRAINING_ID, 
     max(RECORD_ID) AS RECORD_ID, 
     max(NID) AS NID
 FROM
     v
 GROUP BY
     ID ;

This would be the intermediate result:

 ID        | LETTER_ID | MEDICAL_ID | SICKNESS_ID |  PRO_ID | EDU_ID | TRAINING_ID | RECORD_ID |    NID
 :-------- | --------: | ---------: | ----------: | ------: | -----: | ----------: | --------: | -----:
 MI1390033 |    299080 |     309609 |        null |  105419 | 659654 |       44917 |     94272 | 161077
 MI1390454 |      null | null       |        null | 4673735 |   null |       47584 |    154745 |   null

At this point, you can select only the rows with (Letter, pro, record, education) not being null:

 SELECT
    *
 FROM
    (SELECT
         ID, 
         max(LETTER_ID) AS LETTER_ID,
         max(MEDICAL_ID) AS MEDICAL_ID,
         max(SICKNESS_ID) AS SICKNESS_ID, 
         max(PRO_ID) AS PRO_ID, 
         max(EDU_ID) AS EDU_ID, 
         max(TRAINING_ID) AS TRAINING_ID, 
         max(RECORD_ID) AS RECORD_ID, 
         max(NID) AS NID
     FROM
         v
     GROUP BY
         ID   
    ) AS flatten
 WHERE
        LETTER_ID is not null 
    and PRO_ID    is not null 
    and RECORD_ID is not null 
    and EDU_ID    is not null ;

You will get:

 ID        | LETTER_ID | MEDICAL_ID | SICKNESS_ID | PRO_ID | EDU_ID | TRAINING_ID | RECORD_ID |    NID
 :-------- | --------: | ---------: | ----------: | -----: | -----: | ----------: | --------: | -----:
 MI1390033 |    299080 |     309609 |        null | 105419 | 659654 |       44917 |     94272 | 161077
 

dbfiddle here

If you're only interested in the ID value, but not the rest, you can use a different approach:

SELECT DISTINCT
    ID
FROM
    v AS v0
WHERE
        EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND LETTER_ID is not null) 
   AND  EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND PRO_ID is not null) 
   AND  EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND RECORD_ID is not null) 
   AND  EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND EDU_ID is not null) ;

dbfiddle here