How to check values for records from different rows

Question

I have a view I want to select those records whose (Letter, pro, record, and education) column values are not null.

For details, I want to check record MI13900454 that his (Letter, pro, record, and education) should not be null. Note: if we select ... from xxx where record_id is not null and letter_id is not null ..... it is not correct because values are not in same row - they are in different rows and columns.

enter image description here

---

Plaintext table:

| ID | LETTER_ID | MEDICAL_ID | SICKNESS_ID | PRO_ID | EDU_ID | TRAINING_ID | RECORD_ID | NID |
|-----------|-----------|------------|-------------|---------|--------|-------------|-----------|--------|
| MI1390454 | | | | | | 47584 | | |
| MI1390454 | | | | 4673735 | | | | |
| MI1390033 | | | | 105419 | | | | |
| MI1390033 | | | | | | | | |
| MI1390033 | | | | | | | | |
| MI1390033 | 299080 | | | | | | | |
| MI1390033 | | | | | 659652 | | | |
| MI1390033 | | 309609 | | | | | | |
| MI1390033 | | | | | | | | |
| MI1390033 | | | | | | | | |
| MI1390033 | | | | | 659650 | | | |
| MI1390033 | | | | | 659654 | | | |
| MI1390033 | | | | | | | | 161077 |
| MI1390033 | | 309608 | | | | | | |
| MI1390033 | | | | | 659651 | | | |
| MI1390454 | | | | | | | 154745 | |
| MI1390033 | | | | | 659653 | | | |
| MI1390033 | | | | | | | | |
| MI1390033 | | | | | | | | |
| MI1390033 | | | | | | | 94272 | |
| MI1390033 | | | | | | 44917 | | |
| MI1390033 | | | | 105418 | | | | |

• Michael Green: Can a row have a value in more than one column, apart from the ID column?
• Michael Green: Is it OK to have multiple rows for each checked column? It appears MI1390033 has many EDU_ID values, for example. Is that valid condition that at least one value should exist?

Answer 1

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

• joanolo: If you think this answer is right for your use-case, it is customary to mark it as "accepted", so that other people can concentrate on the yet unresolved questions.

Answer 2

Try this :

Select * from view_name
where
(LETTER_ID is not null and
 PRO_ID is not null and
 RECORD_ID is not null and
 EDU_ID is not null)

• Ali: Sorry if you try this query there is no result.

Answer 3

WHERE [ThisColumn] IS NOT NULL OR [ThatColumn] IS NOT NULL

Don't expect good performance, because that table design does not adhere to a relational database's normalized forms, other than 1NF.