Styjun

How to describe POV characters?

Why was Mal so quick to drop Bester in favour of Kaylee?

I just started should I accept a farewell lunch for a coworker I don't know?

What does grep -v "grep" mean and do?

How to properly say asset/assets in German

How do we separate rules of logic from non-logical constraints?

Is there reliable evidence that depleted uranium from the 1999 NATO bombing is causing cancer in Serbia?

Adjective for 'made of pus' or 'corrupted by pus' or something of something of pus

13th chords on guitar

Do home values typically rise and fall at a consistent percent?

Comment traduire « That screams X »

Are the requirements of a Horn of Valhalla cumulative?

Can I travel from Germany to England alone as an unaccompanied minor?

My colleague is constantly blaming me for his errors

Movie with Zoltar in a trailer park named Paradise and a boy playing a video game then being recruited by aliens to fight in space

Converting Geographic Coordinates into Lambert2008 coordinates

How did installing this RPM create a file?

Why do I need two parameters in an HTTP parameter pollution attack?

If two black hole event horizons overlap (touch) can they ever separate again?

How can I deal with extreme temperatures in a hotel room?

I hit a pipe with a mower and now it won't turn

Does a return economy-class seat between London and San Francisco release 5.28 tonnes of CO2 equivalents?

Why were the first airplanes "backwards"?

How to securely dispose of a smartphone?

Get records where all the corresponding columns of the same column is null

Get list of all tables in Oracle?Can I concatenate multiple MySQL rows into one field?Altering a column: null to not nullHow can I get column names from a table in SQL Server?Retrieving the last record in each group - MySQLSQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraintsFinding duplicate values in a SQL tableWhat are the options for storing hierarchical data in a relational database?Find all tables containing column with specified name - MS SQL ServerGet size of all tables in database

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

My data looks like this

| id | Failure 
+----------------+-----------
| 1 | null 
| 1 | null 
| 1 | null 
| 1 | abc 
| 1 | null 
| 2 | null
| 2 | null 
| 2 | null 
| 2 | abc 
| 2 | null
| 3 | null
| 3 | null 
| 3 | null 
| 3 | null 
| 3 | null

Now I need to get the id when all the failure column data has null for that id.

Expected result:

| id | Failure 
+----------------+----------
| 3 | null
| 3 | null 
| 3 | null 
| 3 | null 
| 3 | null

sql sql-server sql-server-2012

share|improve this question

edited Mar 25 at 14:14

Gordon Linoff

826k3838 gold badges336336 silver badges443443 bronze badges

asked Mar 25 at 14:09

Kalyan VarmaKalyan Varma

2144 bronze badges

add a comment | 

0

My data looks like this

| id | Failure 
+----------------+-----------
| 1 | null 
| 1 | null 
| 1 | null 
| 1 | abc 
| 1 | null 
| 2 | null
| 2 | null 
| 2 | null 
| 2 | abc 
| 2 | null
| 3 | null
| 3 | null 
| 3 | null 
| 3 | null 
| 3 | null

Now I need to get the id when all the failure column data has null for that id.

Expected result:

| id | Failure 
+----------------+----------
| 3 | null
| 3 | null 
| 3 | null 
| 3 | null 
| 3 | null

sql sql-server sql-server-2012

share|improve this question

edited Mar 25 at 14:14

Gordon Linoff

826k3838 gold badges336336 silver badges443443 bronze badges

asked Mar 25 at 14:09

Kalyan VarmaKalyan Varma

2144 bronze badges

add a comment | 

My data looks like this

| id | Failure 
+----------------+-----------
| 1 | null 
| 1 | null 
| 1 | null 
| 1 | abc 
| 1 | null 
| 2 | null
| 2 | null 
| 2 | null 
| 2 | abc 
| 2 | null
| 3 | null
| 3 | null 
| 3 | null 
| 3 | null 
| 3 | null

Now I need to get the id when all the failure column data has null for that id.

Expected result:

| id | Failure 
+----------------+----------
| 3 | null
| 3 | null 
| 3 | null 
| 3 | null 
| 3 | null

sql sql-server sql-server-2012

share|improve this question

edited Mar 25 at 14:14

Gordon Linoff

826k3838 gold badges336336 silver badges443443 bronze badges

asked Mar 25 at 14:09

Kalyan VarmaKalyan Varma

2144 bronze badges

| id | Failure 
+----------------+-----------
| 1 | null 
| 1 | null 
| 1 | null 
| 1 | abc 
| 1 | null 
| 2 | null
| 2 | null 
| 2 | null 
| 2 | abc 
| 2 | null
| 3 | null
| 3 | null 
| 3 | null 
| 3 | null 
| 3 | null

| id | Failure 
+----------------+----------
| 3 | null
| 3 | null 
| 3 | null 
| 3 | null 
| 3 | null

sql sql-server sql-server-2012

sql sql-server sql-server-2012

share|improve this question

edited Mar 25 at 14:14

Gordon Linoff

826k3838 gold badges336336 silver badges443443 bronze badges

asked Mar 25 at 14:09

Kalyan VarmaKalyan Varma

2144 bronze badges

share|improve this question

edited Mar 25 at 14:14

Gordon Linoff

826k3838 gold badges336336 silver badges443443 bronze badges

asked Mar 25 at 14:09

Kalyan VarmaKalyan Varma

2144 bronze badges

edited Mar 25 at 14:14

Gordon Linoff

826k3838 gold badges336336 silver badges443443 bronze badges

edited Mar 25 at 14:14

Gordon Linoff

826k3838 gold badges336336 silver badges443443 bronze badges

asked Mar 25 at 14:09

Kalyan VarmaKalyan Varma

2144 bronze badges

asked Mar 25 at 14:09

Kalyan VarmaKalyan Varma

2144 bronze badges

2 Answers
2

active

oldest

votes

3

If you just want the ids, use aggregation:

select id
from t
group by id
having max(failure) is null;

I don't see a reason to get all the repetitive rows. If you want them, then I suggest not exists:

select t.*
from t
where not exists (select 1
 from t t2
 where t2.id = t.id and t2.failure is not null
 );

share|improve this answer

answered Mar 25 at 14:14

Gordon LinoffGordon Linoff

826k3838 gold badges336336 silver badges443443 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  i actually need the count of distinct field id above query is not giving count
  
  – Kalyan Varma
  Mar 25 at 14:22
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @KalyanVarma . . . Nothing in this question refers to a count. If you have another question, you should ask it as a new question (if you change this one, you may invalidate answers).
  
  – Gordon Linoff
  Mar 25 at 15:03
  

  
  
  
  

add a comment | 

0

 Select * 
 from Table 
 where Id not in (select id from Table where failure is not null)

share|improve this answer

answered Mar 25 at 14:16

Amit KumarAmit Kumar

14988 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi! While this may be an answer to the question (untested), code only answers are discouraged on SO. Please consider adding an explanation to your answer to help OP better understand, and to provide greater benefit to future visitors of the site. Thanks!
  
  – d_kennetz
  Mar 25 at 14:53
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55339726%2fget-records-where-all-the-corresponding-columns-of-the-same-column-is-null%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

3

If you just want the ids, use aggregation:

select id
from t
group by id
having max(failure) is null;

I don't see a reason to get all the repetitive rows. If you want them, then I suggest not exists:

select t.*
from t
where not exists (select 1
 from t t2
 where t2.id = t.id and t2.failure is not null
 );

share|improve this answer

answered Mar 25 at 14:14

Gordon LinoffGordon Linoff

826k3838 gold badges336336 silver badges443443 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  i actually need the count of distinct field id above query is not giving count
  
  – Kalyan Varma
  Mar 25 at 14:22
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @KalyanVarma . . . Nothing in this question refers to a count. If you have another question, you should ask it as a new question (if you change this one, you may invalidate answers).
  
  – Gordon Linoff
  Mar 25 at 15:03
  

  
  
  
  

add a comment | 

3

If you just want the ids, use aggregation:

select id
from t
group by id
having max(failure) is null;

I don't see a reason to get all the repetitive rows. If you want them, then I suggest not exists:

select t.*
from t
where not exists (select 1
 from t t2
 where t2.id = t.id and t2.failure is not null
 );

share|improve this answer

answered Mar 25 at 14:14

Gordon LinoffGordon Linoff

826k3838 gold badges336336 silver badges443443 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  i actually need the count of distinct field id above query is not giving count
  
  – Kalyan Varma
  Mar 25 at 14:22
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @KalyanVarma . . . Nothing in this question refers to a count. If you have another question, you should ask it as a new question (if you change this one, you may invalidate answers).
  
  – Gordon Linoff
  Mar 25 at 15:03
  

  
  
  
  

add a comment | 

If you just want the ids, use aggregation:

select id
from t
group by id
having max(failure) is null;

I don't see a reason to get all the repetitive rows. If you want them, then I suggest not exists:

select t.*
from t
where not exists (select 1
 from t t2
 where t2.id = t.id and t2.failure is not null
 );

share|improve this answer

answered Mar 25 at 14:14

Gordon LinoffGordon Linoff

826k3838 gold badges336336 silver badges443443 bronze badges

select id
from t
group by id
having max(failure) is null;

select t.*
from t
where not exists (select 1
 from t t2
 where t2.id = t.id and t2.failure is not null
 );

share|improve this answer

answered Mar 25 at 14:14

Gordon LinoffGordon Linoff

826k3838 gold badges336336 silver badges443443 bronze badges

answered Mar 25 at 14:14

Gordon LinoffGordon Linoff

826k3838 gold badges336336 silver badges443443 bronze badges

answered Mar 25 at 14:14

Gordon LinoffGordon Linoff

826k3838 gold badges336336 silver badges443443 bronze badges

0

 Select * 
 from Table 
 where Id not in (select id from Table where failure is not null)

share|improve this answer

answered Mar 25 at 14:16

Amit KumarAmit Kumar

14988 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi! While this may be an answer to the question (untested), code only answers are discouraged on SO. Please consider adding an explanation to your answer to help OP better understand, and to provide greater benefit to future visitors of the site. Thanks!
  
  – d_kennetz
  Mar 25 at 14:53
  

  
  
  
  

add a comment | 

0

 Select * 
 from Table 
 where Id not in (select id from Table where failure is not null)

share|improve this answer

answered Mar 25 at 14:16

Amit KumarAmit Kumar

14988 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi! While this may be an answer to the question (untested), code only answers are discouraged on SO. Please consider adding an explanation to your answer to help OP better understand, and to provide greater benefit to future visitors of the site. Thanks!
  
  – d_kennetz
  Mar 25 at 14:53
  

  
  
  
  

add a comment | 

 Select * 
 from Table 
 where Id not in (select id from Table where failure is not null)

share|improve this answer

answered Mar 25 at 14:16

Amit KumarAmit Kumar

14988 bronze badges

 Select * 
 from Table 
 where Id not in (select id from Table where failure is not null)

share|improve this answer

answered Mar 25 at 14:16

Amit KumarAmit Kumar

14988 bronze badges

answered Mar 25 at 14:16

Amit KumarAmit Kumar

14988 bronze badges

answered Mar 25 at 14:16

Amit KumarAmit Kumar

14988 bronze badges