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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-18 : 20:03:36
|
&y writes "I'm performing a query against (among many other things) two tables. One, tblA, contains information on photos in my photo album. The other (tblB) contains information on how those photos have been rated by users. Photos can have positive or negative ratings depending on the sum of corresponding entries in tblB, and if they haven't been rated as good or bad (1 or -1) they have a neutral rating - zero.
tblA:
pk_ID str_Name
----------------------------
1000 Photo X
1001 Photo Y
1002 Photo Z
tblB:
fk_ID int_Rating
----------------------
1000 1
1000 1
1001 -1
So in the above example, Photo X has a rating of 2, Photo Y has a rating of -1, and Photo Z has a rating of 0. Of course, when I use the query below (truncated considerably for readability) photos without ratings come back with a rating of NULL, not 0. This screws up the order, since NULL comes after both negative and positive numbers.
SELECT DISTINCT a.pk_ID, a.str_Name
(SELECT SUM(int_Rating) FROM tblB WHERE fk_ID = a.pk_ID) AS "ratingSum"
FROM tblA a
LEFT JOIN tblB b
ON a.pk_ID = b.fk_ID
ORDER BY ratingSum DESC
How can I construct this query so that a NULL ratingSum is treated as zero in the ORDER BY clause? There will be a LOT more going on in this query ultimately, but specifically this is what I'm having trouble with." |
|
|
|
|
|
Replacing NULL with 0 for ORDER BY