I have a database of products, with each product having several categories that it can belong in (many-to-many relationship). A product can also have some photographs (many-to-many relationship)I am trying to do a search to list all products, and want to include 1 photo per product in the results. When I run the following code I get an error. Could anybody recommend the correct way to achieve this result?Please note that database admin's can set a particular product photo to be 'featured', meaning that that photo always appears for a product. This is why this clause exists in the nested select statement.Thanks in advance. 
SELECT p.*, s.statusDesc, m.ManufacturerName, c.categoryName, (SELECT TOP 1 imageID, productID FROM tblProductImages WHERE productID = p.ID ORDER BY featured DESC, imageID DESC) AS imageID FROM tblProducts AS p INNER JOIN tblCategoriesProducts cp ON p.ID = cp.ProductID INNER JOIN tblStatus AS s ON p.statusID = s.ID LEFT OUTER JOIN tblManufacturers AS m ON p.ManufacturerID = m.ID INNER JOIN tblCategories AS c ON cp.CategoryID = c.ID
Only one expression can be specified in the select
Posted - 2008-12-15 : 13:27:45
