Optional innerjoin parameter?

Question

Hi I am looking for a way to make an INNER JOIN optional, say when the user doesn't put in a zip code parameter. This is my SQL. The #TempZips section is what I am focusing on.

SELECT * FROM (Users INNER JOIN Photos ON Users.UserID = Photos.UserID)
			INNER JOIN Profile ON Users.UserID = Profile.UserID 
			INNER JOIN #TempZips Zips ON (Zips.ZipCode = Profile.Zip OR Zips.ZipCode = null)
			WHERE Profile.Age BETWEEN 18 AND 99 AND 
			(Profile.Zip IS NOT NULl)

Answer 1

You can change the INNER JOIN to LEFT OUTER JOIN, as follows

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 18 AND 99 AND 
			(Profile.Zip IS NOT NULl)

---

Abdallah, PMP, ITIL, MCTS