SELECT only NOT NULL records. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT only NOT NULL records.

Author

Topic

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-03-02 : 08:08:20

I have the following SP:-

CREATE PROCEDURE [dbo].[usp_Favourites]
@Username Varchar(30), @Password Varchar (30), @Postcode Varchar (30)
AS
SELECT favourites1, favourites2, favourites3, favourites4, favourites5, favourites6
FROM Users
WHERE UserID = (SELECT UserID From Logins
WHERE Username = @Username AND Password = @Password AND Postcode = @Postcode)

This obviously runs ok and pulls out all the data from the 6 'favourite' columns. My problem is that some or all of these colums may contain NULL values which I do not want to be returned. I have tried various ways to return only the data that IS NOT NULL but cannot do it.
Can anyone help please?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-02 : 08:15:37

If u don't want to return the whole record when one or more fields is null:
U may use
... field1 is Not Null and Filed2 is not null or ...

As in


SELECT favourites1, favourites2, favourites3, favourites4, favourites5, favourites6
FROM Users
WHERE UserID = (favourites1 is not null and favourites2 is not null and ... ) and (SELECT UserID From Logins
WHERE Username = @Username AND Password = @Password AND Postcode = @Postcode)

If u don't want to see NULL field, instead you wish to see a blank
U may use IsNull function

Kristen
Test

22859 Posts

Posted - 2006-03-02 : 08:17:52

Would it be acceptable to return them as a single comma delimited list column?


SELECT [MyFavourites] = RTRIM(COALESCE(favourites1+' ', '')
                      + COALESCE(favourites2+' ', '')
...
                      + COALESCE(favourites6, '')

You could add a condition to the WHERE clause to not return any rows where ALL 6 favourites were NULL.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-02 : 08:19:06

You should post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-03-02 : 08:31:55

Hi Guys,

I want only records that are NOT NULL to be returned, any of the six columns that contain a NULL value should not be returned.
EG. If Favourites1 = Red, Favourites2 = Blue, Favourites3 = NULL, Favourites4 = Green, Favourites5 = NULL, Favourites6 = NULL then only Favourites1, Favourites2 and Favourites4 would be returned.

I cannot use AND in the WHERE clause as Srinika highlighted as this would exclude all the records.

Pat.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-02 : 08:37:46

Where do you want to show the data?
If you use Front End application, you can write query that returns data to it and check if the column is Null and show it accordingly

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-02 : 08:52:24

Also, please consider redesigning your database properly if you can. It is completely unnormalized and that's what gives you NULLs everywhere and what makes somewhat simple things like this difficult to write.

See:

http://www.datamodel.org/NormalizationRules.html

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-03-02 : 09:20:10

The Database has been designed properly. These columns are provided so a user can store six options that they use regularly. They can then be chosen in the Front-End app as a big timesaver. This is why I don't want the NULLS to be selected. A user may only decide to use 2 or 3 of these columns leaving NULL values for the rest. As for your comment about "Nulls everywhere", these are in fact the only nullable columns in the Table. Can you please tell me why this constitutes such bad database design?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-02 : 09:40:18

EDIT: Actually, I misspoke -- I don't know if the database itself has a bad design, but this particular table does.

Are you familiar with normalization?

If you want to store multiple items for each entity, those items should be stored in multiple ROWS in a related table, not in multiple columns.

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-03-02 : 09:40:49

The silence obviously answers my question

So, regarding my original question does anyone know of a way I can eliminate the NULL's please.

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-03-02 : 09:41:53

Yes, very. Can you offer anything more constructive please?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-02 : 09:54:54

Still you didnt tell us which Front End you are using. If you think you have no control to change the table structure,You can very well do this in your Front End application by checking the Null value

If Isnull(rs("favourites1")) then
--dont show
end if
If Isnull(rs("favourites2")) then
--dont show
end if
.
.
.

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-02 : 09:55:46

What you are asking for is returning a dynamic number of columns in a stored proc, which makes no sense. Columns should be constants -- the only thing that should change is the # of rows returned and the contents. Kristen had a good idea if you want 1 single row. But to use a database properly, you should return a list of Favorites and let the presentation layer do what it wants in terms of formatting (skip nulls, put them on 1 line, separate them with commas, etc).

My "constructive" advice is to take your redundant, unnormalized columns OUT of your Users table and put them in a properly related table, one per row.

Favorites (UserID, Favorite) -- with a PK of (userID/Favorite) as well.

then, to return favorites for a user, you simply say:


Select Favorites.UserID, Favorites.Favorite
from Favorites
inner join Logins
  ON Favorites.UserID = LogIns.UserID
where
     LogIns.Username = @Username AND 
     LogIns.Password = @Password AND 
     LogIns.Postcode = @Postcode

Does that make sense? If a user has 2 favorites, there are two rows in the UserFavorites table for him. If he has 1, there's one, if he has 5, there are 5. You don't need to worry about nulls. If you need help limiting the user to 5 favorites using RI, let me know.

Also, to maintain favorites in now you just INSERT or DELETE them from this table. Done. Sorting it easy. Add nire attributes for each favorite is handled easily.

With your current design, when adding you need to find the first non-null column and put it there, or shift things over. Sorting is impossible. Adding extra information for each favorite is impossible as well.

I know that you mention that you are very familiar with normalization, and this is all pretty basic stuff, but if you have any questions let me know. take the time to design things properly and going forward things will be much easier.

Kristen
Test

22859 Posts

Posted - 2006-03-02 : 09:58:50

"any of the six columns that contain a NULL value should not be returned"

You can't do that. If you want to have 6 columns in your resultset you will get 6 columns on every row. You cannot have only 5 columns on some of the rows ...

You could select between 0 and 6 ROWS, each row containing a Favourite - would that do?

(If the answer is yes then the easiest way is to redesign the table so that the 6 favourite columns are rows in a related table, not columns in the main table, as jsmith8858 said)

Kristen

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-02 : 10:11:21

quote:
Originally posted by p.shaw3@ukonline.co.uk

Hi Guys,

I want only records that are NOT NULL to be returned, any of the six columns that contain a NULL value should not be returned.
EG. If Favourites1 = Red, Favourites2 = Blue, Favourites3 = NULL, Favourites4 = Green, Favourites5 = NULL, Favourites6 = NULL then only Favourites1, Favourites2 and Favourites4 would be returned.

I cannot use AND in the WHERE clause as Srinika highlighted as this would exclude all the records.

Pat.

Can v see some sample data (to cover as many as ur real scenario) and expected output ?
Do u have only one record returned in any case?
Otherwise if there r records as following:
Rec1 : Fav1 = Red, Fav2 = Blue, Fav3 = NULL, Fav4 = Green, Fav5 = NULL, Fav6 = NULL
Rec2 : Fav1 = Null, Fav2 = Red, Fav3 = NULL, Fav4 = Null, Fav5 = NULL, Fav6 = Red

What should it return?

If u r sure that, it to do with one record as return
Just a suggestion:

U Run ur query with Nulls / Not Nulls returned and fill the result to a temp table and then query that table and create a dynamic SQL and execute it.

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2006-03-02 : 10:12:22

Points taken gratefully guys, lesson learned. Many thanks to you all for your input.

Pat.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-02 : 10:28:11

Once u filled the temp table (T1) with return of ur query following code will return ur required o/p
[note that I used abbreviated names for tbl 7 cols]

declare @s varchar(8000)

Set @s = 'Select '
Select @s = @s + 'F1,' from T1 where F1 is not null
Select @s = @s + 'F2,' from T1 where F2 is not null
Select @s = @s + 'F3,' from T1 where F3 is not null
Select @s = @s + 'F4,' from T1 where F4 is not null
Select @s = @s + 'F5,' from T1 where F5 is not null
Select @s = @s + 'F6,' from T1 where F6 is not null
set @s = left(@s, Datalength(@s)-1)
set @s = @s + ' From T1'
Exec (@s)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources