SQL - Selecting rows which have a value

Question

I have a table which consists of a lot of columns and rows. The problem I have is that I want to select a certain column and get the row with all the non-null values.

My (so far) correct statement looks like this:

SELECT * 
FROM table_name
WHERE col_1 = "Apotheken"

This returns the correct row with all its (non)values. But how do I get just the non-null-values out of this row? I could write something like this:

SELECT * 
FROM table_name
WHERE col_1 = "Apotheken"
AND col_2 IS NOT NULL
AND col_3 IS NOT NULL
...
AND col_n IS NOT NULL

As I stated there are way too many columns. Is there a way to select just the columns with an actual value?

Thanks in advance!

You can't conditionally select a column in a non dynamic query. You would need dynamic SQL for this I believe. But you can handle this in your presentation layer. — Tim Biegeleisen, May 19 '17 at 08:52
@Malte Hartwig I know, but I need all the columns in that certain row (col_1 = "Apotheken") — Allix, May 19 '17 at 09:01
It is a duplicate of http://stackoverflow.com/questions/5285448/mysql-select-only-not-null-values. — Galcoholic, May 19 '17 at 09:02
Yeah, well, the "solutions" and suggestions don't apply here. The table is normalized. I just want to know if it's possible to select those values without handling this issue in code (or type out the >20 columns by hand). — Allix, May 19 '17 at 09:18
Maybe the problem is, that your columns are `NULLABLE`? Is `ALTER TABLE column_4 SET NOT NULL` not possible? If you are just querying, you could just `CREATE VIEW` with the `col_N IS NOT NULL` hidden in the `VIEW`? — flutter, May 19 '17 at 09:43
You want only records where all fields are non-Null? Are you using Access query designer? Don't have to 'type out' columns, drag them down to design grid. Copy/paste 'Is Not Null' into Criteria cell under each. — June7, May 19 '17 at 10:03

Gustav · Answer 1 · 2017-05-19T10:42:41.253

0

You can do it this way which will save some typing:

SELECT * 
FROM table_name
WHERE col_1 = "Apotheken"
AND (col_2 + col_3 + col_4 + ... + col_n) IS NOT NULL

Or as a view for multiple uses:

SELECT * 
FROM table_name
WHERE (col_2 + col_3 + col_4 + ... + col_n) IS NOT NULL

edited May 19 '17 at 10:42

answered May 19 '17 at 09:48

Gustav

53,498
7
29
55

Thank you guys for the suggestions. Apparently you can't create views with MS Access. I tried the "ugly" way with `WHERE col_1 = "Apotheken" AND col_2 IS NOT NULL` but it just returns all the columns of the row without the values. Why is that? :/ – Allix May 19 '17 at 12:54

score 0 · Answer 2 · answered May 19 '17 at 09:48

CREATE VIEW

You can just create a VIEW to get rid of typing out these conditions in every query you write.

CREATE VIEW view_table_not_null AS (  
  SELECT *  
  FROM table  
  WHERE col_1 IS NOT NULL  
    AND col_2 IS NOT NULL  
    AND col_N IS NOT NULL  
);

Then you can SELECT from the VIEW without writing the WHERE ... IS NOT NULL in every query to get the NOT NULL's for free. Create once, use everywhere. The classic VIEW.

SELECT *  
FROM view_table_not_null  
WHERE col_3 = 'Apotheken';

Change columns to NOT NULL

ALTER TABLE column_4 SET NOT NULL;

SQL - Selecting rows which have a value

2 Answers2