Recently I was working on a project where I had to return a simple list of products, and if they had images associated with them, return information about only one along with the product. Times like these, a simple left join or inner join just doesn’t cut it if there are more than one image/record per product. When this happens there is almost always a simple solution, so let’s take a look.

Thar be Dragons!

Too many times i have seen junior developers do silly things like run a SQL command on each iteration of a dataset loop. This will obviously end in slow ugly code that places a great amount of stress on your SQL server when there is no need. This is a very bad practice that should be avoided at all costs.

Additionally this same outcomes can easily come from using an ORM tool badly when calling child objects or related objects (ie. some pseudo code like objProduct.Image[0].ImageSource). This is an equally bad mistake to make, but people seem to fall into it easily once they have their data layer abstracted far far away in another land as it may seem.

The SQL Prize is in the pudding

One option is to use a SQL sub query, which depending on your situation may also work. Our answer to the this niggling issue in this case is in the SQL statement OUTER APPLY.

A quick Google will show you some cool examples and uses:

http://weblogs.sqlteam.com/jeffs/archive/2007/06/12/60228.aspx

http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

My example SQL

So as usual, lets get on with the show!

SELECT tblProducts.prodID, 
        tblProducts.prodName,
        tblProducts.prodDescription,
        tblImages.imgFileName
FROM tblProducts   
OUTER APPLY (  
    Select TOP 1 images.imgFileName  
    From tblImages images 
    WHERE images.prodID = tblProducts.prodID  
    ) tblImages