2

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

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 '17 at 12:56
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)
  • Sorry if you try this query there is no result. – Ali May 20 '17 at 6:50
-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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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