Query help

Question

I need help with this query below. Say if the user provides Genre as "No preference" which is NULL, s/he puts in a specific zip code. I just want to retrieve all users within that zip code's distance ONLY regardless the genre. Right now, it's pulling everyone regardless the distance.

		SELECT * FROM (Users INNER JOIN Photos ON Users.UserID = Photos.UserID)
			INNER JOIN Profile ON Users.UserID = Profile.UserID 
			LEFT OUTER JOIN #TempZips Zips ON Zips.ZipCode = Profile.Zip
			WHERE Profile.Age BETWEEN @Age1 AND @Age2 AND 
			(Profile.Genre = @Genre OR @Genre IS NULL)

Answer 1

It's because of the LEFT JOIN.  Change that to an INNER JOIN and it will only do the specific zip code(s) that are in #TempZips.

---

--Brad (My Blog)

Answer 2

That's what I thought. But I cannot change that logic because if the User does not provide the zip code. It won't display anything. I want the zip code to be also optional/null.

Answer 3

Then you'll have to have two different queries...

IF EXISTS(SELECT * FROM #TempZips)
  SELECT ... INNER JOIN #TempZips ...
ELSE
  SELECT ...  --No inclusion of #TempZips in the query at all

---

--Brad (My Blog)

Answer 4

Try this:

SELECT <columns>
FROM Users AS U
INNER JOIN Photos AS P
   ON U.UserID = P.UserID
INNER JOIN Profile AS F
   ON U.UserID = F.UserID 
WHERE F.Age BETWEEN @Age1 AND @Age2
  AND (F.Genre = @Genre 
    OR (@Genre IS NULL 
    AND EXISTS(SELECT *
               FROM #TempZips AS Z
               WHERE Z.Zipcode = F.Zip)));

---

Plamen Ratchev

Answer 5

With that query it only works if the user provides the Zip code, but not when they don't provide the zip code AND choose "no preference" NULL. But half way what I was looking for though.

Answer 6

I think I got it.

SELECT <columns>
FROM Users AS U
INNER JOIN Photos AS P
   ON U.UserID = P.UserID
INNER JOIN Profile AS F
   ON U.UserID = F.UserID 
WHERE F.Age BETWEEN @Age1 AND @Age2
  AND (F.Genre = @Genre 
    OR @Genre IS NULL) 
    AND (EXISTS(SELECT *
               FROM #TempZips AS Z
               WHERE Z.Zipcode = F.Zip OR @Zip IS NULL);

Answer 7

It seemed you want to check for Zip only when Genre is NULL. This will make both Genre and Zip optional:

SELECT <columns>
FROM Users AS U
INNER JOIN Photos AS P
   ON U.UserID = P.UserID
INNER JOIN Profile AS F
   ON U.UserID = F.UserID 
WHERE F.Age BETWEEN @Age1 AND @Age2
  AND (F.Genre = @Genre OR @Genre IS NULL)
  AND (EXISTS(SELECT *
              FROM #TempZips AS Z
              WHERE Z.Zipcode = F.Zip)
    OR NOT EXISTS(SELECT * FROM #TempZips));

---

Plamen Ratchev

Answer 8

It seemed you want to check for Zip only when Genre is NULL. This will make both Genre and Zip optional:

SELECT <columns>
FROM Users AS U
INNER JOIN Photos AS P
   ON U.UserID = P.UserID
INNER JOIN Profile AS F
   ON U.UserID = F.UserID 
WHERE F.Age BETWEEN @Age1 AND @Age2
  AND (F.Genre = @Genre OR @Genre IS NULL)
  AND (EXISTS(SELECT *
              FROM #TempZips AS Z
              WHERE Z.Zipcode = F.Zip)
    OR NOT EXISTS(SELECT * FROM #TempZips));

---

Plamen Ratchev

Hehe... same as what I have. But I appreciate you lead me to the right direction. Marking this as RESOLVED.

Answer 9

This changes things, you did not mention there is a @Zip variable...

---

Plamen Ratchev

Answer 10

Are you saying my SQL would not work? In what scenario? Yeah, sorry about not mentioning the @Zip parameter. 

Answer 11

Somehow it seems strange you use the @Zip variable only as a flag if there should be match by Zip. More logical is to be part of the filter, maybe like:

WHERE (Z.Zipcode = F.Zip AND F.Zip = @Zip) OR @Zip IS NULL

---

Plamen Ratchev

Answer 12

Oh I have another function that needs to get that @Zip parameter to calculates distance and stuff. It then is stored in the #TempZips folder. http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e9a7188f-391f-4757-9c03-c8955b8117c1