Select Query in SQL + All the values in columns

Question

I am having a table Table1 with columns id1, id2, id3 all the columns are nullable

I may enter null or value to all columns in rows.

My question is I need to select the rows whose all the column values should not be null.

Thanks

Answer 1

The answer to use a "function" to test the null-values is correct. The syntax depends on the database. If ISNULL() does not exist in your database then try:

SELECT * FROM Table1 WHERE id1 IS NOT NULL AND id2 IS NOT NULL AND id3 IS NOT NULL

And there is no way to short this down even if you have 300 fields in your table.

Answer 2

There are totally around 300 columns in the table. I can't do the is null property for all the columns in where condition.

Answer 3

Best bet is to either rethink the design of your tables, splitting them if required.

otherwise best bet is do it progmatically - grab the table metadata, itterate through the columns and drynamically create the SQL from there. Most coding languages have access to the tables metadata, failing that a second SQL is required for it.

But, best bet is to think how can I design the table better.

Answer 4

you can try CLR stored procedure (if you're using SQL Server) or move this logic to the other layer of your application using C# or whatever language you're using.

another option is to create the query dynamically, concatenating your WHERE clause and EXECute your dynamically generated query.

Answer 5

Don't understand why this question is getting negitive reviews - This question can be extended to people who inherited a large table from a non-programmer in a community (I know from previous experience), and likewise if the table is unknown. To downgrade this because its '300' columns is pointless IMO.

Answer 6

Are you just reading the data, or will you want to try and update the rows in question?

I'm just wondering if there's something you can go by making a half-dozen views, each one based on say 50 columns being NOT NULL, and then linking them with some kind of EXISTS or UNION statement?

Can you tell us a bit more about what you want to do with your result set?

Answer 7

Exists or Union wont works

consider my table

create table employees ( id int not null, name varchar(20) null, description varchar(30) null );

records are

insert into employees values (1,'Name','Hai'); insert into employees values (2,null,null) insert into employees values(3,null,null)

Requirement:

I need the first value. ie i need to select only the not nullable rows in all of its column in a entire table.

This is the sample table that i have given consider 300 columns and 700,000 records. we can't use isnull in where condition

Is there any other way to query this?

Thanks

Answer 8

You need to do this:

SELECT *
FROM yourtable
WHERE
    column1 IS NOT NULL
AND column2 IS NOT NULL
AND column3 IS NOT NULL
AND ....

Answer 9

How can i do this

SELECT * FROM yourtable WHERE column1 IS NOT NULL AND column2 IS NOT NULL AND column3 IS NOT NULL AND ....

for all the columns its good way of quering rite...

Answer 10

Sorry - I might be being a bit thick here. You're trying to get back the rows that have got SOMETHING in one of the columns (other than the id column)?

Can't you do;

create vw_View_Fields1to5 as 
  select id from employees 
  where name is not null or description is not null or field3 is not null 
  or field4 is not null or field5 is not null;
create vw_View_Fields6to10 as 
  select id from employees 
  where field6 is not null or field7 is not null or field8 is not null 
  or field 9 is not null or field10 is not null;
(etc)

select id from vw_View_Fields1to5
union 
select id from vw_View_Fields6to10 .... (etc)

You'd have to take a DISTINCT or something to cut down the rows that fall into more than one view, of course.

If you want the rows back that have NOTHING in any column other than id, you'd switch 'or blah is not null' to be 'and blah is null' (etc).

Does that make sense... or am I missing something? :-)

EDIT: Actually, I believe the UNION process will only bring back distinct rows anyway (as opposed to UNION ALL), but I could be wrong - I haven't actually tried this.... (yet!)

Answer 11

For the first time whatever Georgi or engram or robsoft is the way. However for subsequent stuff you can if possible alter the table and add one more column, called CSELECTFLAG, and initially updated this to Y for all columns that have values and N for others. Everytime there is an insert this needs to be updated. This would help make your subsequent queries faster and easier.