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.

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 |        |             |           |        |

Can a row have a value in more than one column, apart from the ID column? — Michael Green, May 20, 2017 at 10:45
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? — Michael Green, May 20, 2017 at 10:47

joanolo · Accepted Answer · 2017-05-20 10:54:50Z

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

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. — joanolo, May 20, 2017 at 12:56

Prabhat G · Accepted Answer · 2017-05-18 07:31:53Z

1

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)

answered May 18, 2017 at 7:31

Prabhat G

1798 bronze badges

Sorry if you try this query there is no result.
– Ali
May 20, 2017 at 6:50

Add a comment |

Bill · Accepted Answer · 2017-05-18 07:15:01Z

-1

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.

answered May 18, 2017 at 7:15

Bill

1243 bronze badges

Add a comment |

Stack Exchange Network

How to check values for records from different rows

3 Answers 3

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
or ask your own question.

Hot Network Questions

How to check values for records from different rows

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-server or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
or ask your own question.