CODEWITHSUNDEEP
sqlsql-server-2008selectnullexcept
Fabio Abreu
Fabio Abreu

Reputation: 45

Select * Except Null Fields

i'm having a problem with my database. I have the folowing:

create table book
(
    name_b varchaR(50) primary key,
    typee varchaR(50) not null,
    nmbr integer unique not null,
    yeare numeric(4,0) default null,
)

create table story
(
    id integer identity(1,1) primary key,
    name_s varchar(100) not null,
    chars varchar(100) not null,
    code varchaR(200) null,
    artist varchar(100) default null,
    argument varchar(100) default null,
    book_ref_name varchar(50) references book(name_b),
    book_ref_nmbr integer references book(nmbr)
)

insert into story values
('StoryName1','StoryChars1',null,default,default,'BookName1',13),
('StoryName2','StoryChars2',null,default,default,'BookName2',35),
('StoryName3','StoryChars3',null,default,default,'BookName3',125)

insert into book values
('BookName1','Type1',13,default),
('BookName2','Type2',35,default),
('BookName3','Type3',125,default)


UPDATE story
SET code = name_s + '-' + chars + '-' + book_ref_name + '-' + Convert(varchar(50), book_ref_nmbr)
WHERE code IS NULL

how can i make a SELECT statement that selects * from STORY except the null fields. for example if a story has no argumentist/artist i dont want that field to show up.

for example:

the results of the select * from story where name_s = 'StoryName1' would be the folowing table but because those two fields are NULL i would want it to be like this table

for the last table to show up that way i did this select id,name_s,chars,code,book_ref_name,book_ref_nmbr from story where name_s = 'StoryName1'

im trying to do something like this (this code obviously doesnt exist is just an example to try to explain me better) 

select ""notnullcollumns"" from story where name_s = 'StoryName1'

Upvotes: 1

Views: 5124

Answers (4)

onedaywhen
onedaywhen

Reputation: 57093

You could do something like this: 

SELECT id, 
      'code' AS a_type, code AS a_value
  FROM story
UNION
SELECT id, 
      'artist' AS a_type, artist AS a_value
  FROM story
UNION
SELECT id, 
      'argument' AS a_type, argument AS a_value
  FROM story
UNION...

but if that looks good to you then you probably need to change your design.

Upvotes: 0

Adriano Carneiro
Adriano Carneiro

Reputation: 58645

Remember the meaning of *. It's just a substitution for enumerating the projection, AKA, columns.

How would you not select a column in a given row? That makes no sense because your output must have a layout.

You should seriously think of how the output layout would be and then let us know. For now, the question does not make much sense.

Upvotes: 3

bfavaretto
bfavaretto

Reputation: 71939

That is not possible! You examples show two tables with just a single row each, but tables typically have more rows. What if you had a table with two rows, and only one of them had NULL values on those columns?

Now, what you can do is just omit the columns from your SELECT clause as on your example. Or maybe use COALESCE to change NULL values to non-NULL.

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

Why not go for the last select in your question? If you don't want them, then don't add them. Select * will return all fields (columns), regardless of the values.

PS: This smells like a multivalued field and you should avoid them:

chars varchar(100) not null

Upvotes: 0

Related Questions