I recently had to do a Left Join on my SQL statement to get images relating to houses in my database. The database results had to give me the properties, regardless of whether they had images or not hence the Left Join as apposed to an inner join. The problem I had was that just doing something like below would not work. SQL would return all your properties but you get the same image for every house:

SELECT P.PropertyID, P.PropertyRef, PI.ImageFile
FROM Property AS P  
LEFT JOIN(
Select TOP 1 ImageFile, PropertyIDFK
From PropertyImages PI
) PI
P.PropertyID = PI.PropertyIDFK

What I needed to do in this situation was use an OUTER APPLY statement. This then allowed me to return all of my properties with the first image uploaded from the database. Very nice. For more information on the APPLY function in SQL Server 2005 you can read some blogs here or here.

Below is the end SQL statement for using TOP function on an OUTER JOIN:

SELECT P.PropertyID, P.PropertyRef, PI.ImageFile
FROM Property AS P  
OUTER APPLY (
Select TOP 1 ImageFile, PropertyIDFK
From PropertyImages PI
WHERE P.PropertyID = PI.PropertyIDFK
ORDER BY PI.PropertyImagesID
) PI