NULL condition in subquery - 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)

NULL condition in subquery

Author

Topic

Rocko
Starting Member

26 Posts

Posted - 2007-04-27 : 13:52:02

Hello,

I’m trying to build a rating system. I have a query with three sub queries. I would like to check for NULL conditions within those sub queries. However I’m not sure what is the best way to do it. This is how I did it:

(case when (select Rating from RatingUser where RatingUser.userID = 2 and RatingCharacteristics.CharID = RatingUser.CharID and SystemID = 2) is not null then (select Rating from RatingUser where RatingUser.userID = 2 and RatingCharacteristics.CharID = RatingUser.CharID and SystemID = 2) else 0 end) as Score,

It doesn't looks not that well optimized to me. And also I’m not sure if in this scenario SQL will execute the select statement twice?

Below is my entire select statement withought checking for NULL conditions.

SELECT  
Ratings.SystemID, Ratings.CharID, RatingCharacteristics.CharName, 
(SELECT Rating FROM RatingUser WHERE RatingUser.userID = 2 and RatingCharacteristics.CharID = RatingUser.CharID and SystemID = 2) RateUser,
(SELECT COUNT(*) FROM RatingUser WHERE RatingUser.CharID = RatingCharacteristics.CharID) RatedBy,
CONVERT(NUMERIC(7,2), (SELECT SUM(Rating) AS INT FROM RatingUser JOIN RatingSystem ON RatingSystem.SystemID = RatingUser.SystemID JOIN RatingCharacteristics rcs ON rcs.CharID = RatingUser.CharID WHERE RatingUser.SystemID = RatingSystem.SystemID AND RatingUser.CharID = RatingCharacteristics.CharID) 
		       / 
		       CAST( (SELECT COUNT(*) AS FLOAT FROM RatingUser WHERE RatingUser.CharID = RatingCharacteristics.CharID ) AS FLOAT)) Score 
FROM Ratings
LEFT JOIN RatingSystem ON RatingSystem.SystemID = Ratings.SystemID
JOIN RatingCharacteristics ON Ratings.CharID = RatingCharacteristics.CharID
WHERE RatingSystem.SystemID = 1

About my database design, I have four tables:
Ratings with columns SystemID and CharID
RatingUser – stores the results for every user
RatingCharacteristics and RatingSystem – every System may have different characteristics for rate.

And also I feel like my query is not that well optimized. If you have other ideas I will be happy to see it.
Thanks in advance for you help.
Rocco

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-27 : 13:54:40

[code]
(select CASE WHEN Rating IS NULL THEN 0 ELSE Rating END as Score from RatingUser where RatingUser.userID = 2 and RatingCharacteristics.CharID = RatingUser.CharID and SystemID = 2)
[/code]

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Rocko
Starting Member

26 Posts

Posted - 2007-04-27 : 15:21:51

Hi Dinakar

Thanks a lot for this.
However your subquery now return NULL for some records. Do you have an idea why?
I’m also a bit confused how to check for NULL condition in my third subquery using the same approach. Could you help me?

CONVERT(NUMERIC(7,2), (SELECT SUM(Rating) AS INT FROM RatingUser JOIN RatingSystem ON RatingSystem.SystemID = RatingUser.SystemID JOIN RatingCharacteristics rcs ON rcs.CharID = RatingUser.CharID WHERE RatingUser.SystemID = RatingSystem.SystemID AND RatingUser.CharID = RatingCharacteristics.CharID) 
		       /
		      CAST( (SELECT COUNT(*) AS FLOAT FROM RatingUser WHERE RatingUser.CharID = RatingCharacteristics.CharID ) AS FLOAT)) Score

Thanks
Rocco

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources