CODEWITHSUNDEEP
sqlsql-server-2008-r2
Johnny Bones
Johnny Bones

Reputation: 8414

Trouble with a JOIN

I'm trying to pull in data from several tables, one of which has a one-to-many relationship. My SQL looks like this:

SELECT 
  VU.*, 
  UI.UserImg, 
  COALESCE(CI.Interests, 0) as NumInterests 
FROM [vw_tmpUsers] VU 
LEFT JOIN (
  SELECT 
    [tmpUserPhotos].UserID, 
    CASE WHEN MAX([tmpUserPhotos].uFileName) = NULL 
    THEN 'dflt.jpg'
    ELSE Max([tmpUserPhotos].uFileName) 
     END as UserImg 
  FROM [tmpUserPhotos] 
  GROUP BY UserID) UI 
ON VU.UserID = UI.UserID

I've excluded a bunch of LEFT JOINS which follows this, because they're all working properly.

My trouble is, I only want to pull in one image name from the table which has a one-to-many relationship. In order to do this, I've used the MAX function.

My desired result would look like this:

UserID  UserName    UserState   UserZip UserIncome  UserHeight  UserImg
1       Jimbo       NY          10012   2           64          1[Blue hills.jpg
2       Jack        MA          06902   3           66          dflt.jpg
3       Lisa        CT          06820   4           64          dflt.jpg
4       Mary        CT          06791   6           67          4[Natalie.jpg
5       Wanda       CT          06791   6           67          dflt.jpg

but instead it looks like this:

UserID  UserName    UserState   UserZip UserIncome  UserHeight  UserImg
1       Jimbo       NY          10012   2           64          1[Blue hills.jpg
2       Jack        MA          06902   3           66          NULL
3       Lisa        CT          06820   4           64          NULL
4       Mary        CT          06791   6           67          4[Natalie.jpg
5       Wanda       CT          06791   6           67          NULL

It's got to be something wonky in my CASE statement, but I'm not very good with them. I've tried using IS NULL, = NULL, ='NULL', all to no avail.

Can anyone spot what I'm doing wrong? One little tidbit that might shed light; MAX(tmpUserPhotos.uFileName) will only return NULL if there is no matching record in tmpUserPhotos.

Upvotes: 2

Views: 54

Answers (4)

user359040
user359040

Reputation:

The reason your CASE expression is returning nulls even though you have included a check for them is that the expression WHEN MAX([tmpUserPhotos].uFileName) = NULL will never evaluate as true, even when MAX([tmpUserPhotos].uFileName) is null.

This is because nulls are a special type of value in SQL, one which means "value is not known" - almost any comparison with a null value evaluates as null, not true or false.

For this reason, you need to use a function such as coalesce or isnull to check whether the expression evaluates as null; alternately, you could amend your case condition to test whether the expression is null, like so:

CASE WHEN MAX([tmpUserPhotos].uFileName) IS NULL 
    THEN 'dflt.jpg'
    ELSE Max([tmpUserPhotos].uFileName) 
 END as UserImg

Upvotes: 1

fabulaspb
fabulaspb

Reputation: 1263

Look at the using of TOP operator and subquery.

SELECT 
  VU.*, 
  ISNULL((SELECT TOP 1 UP.uFileName FROM tmpUserPhotos AS UP
          WHERE UP.userid = VU.userID), 'dflt.jpg') AS userImg
FROM [vw_tmpUsers] VU

Upvotes: 1

MrVimes
MrVimes

Reputation: 3312

Instead of the case statement, try the 'isnull' function...

isnull(MAX([tmpUserPhotos].uFileName),'dflt.jpg') as UserImg

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You need to check for NULL in the outer query:

SELECT VU.*, 
       COALESCE(UI.UserImg, 'dflt.jpg') as UserImg,
       COALESCE(CI.Interests, 0) as NumInterests 
FROM [vw_tmpUsers] VU LEFT JOIN
     (SELECT [tmpUserPhotos].UserID, MAX([tmpUserPhotos].uFileName) as UserImg 
      FROM [tmpUserPhotos] 
      GROUP BY UserID
     ) UI 
     ON VU.UserID = UI.UserID ;

You are getting NULL values because the LEFT JOIN has no matches. No matches means that the SELECT is not being executed on any data.

I'll also note that if you have a preference for the priority of the images, then OUTER APPLY is easier than LEFT JOIN. However, that would be another question.

Upvotes: 3

Related Questions