SQL Question

[SortedSites]

SQL Question

Tables

Products
ProductID PK
Description
Price

Photos
PhotoID PK
PhotoPath 'URL of the photo on the web server
Product ID FK

A one-to-many relationship exists between the Products table and the photos table, ie: each product can have 0, 1, 2, 3 or more photos associated with it.

How can you perform a query which lists all products and only the first corresponding photo path, yet provides a default values for the Photopath so an "Awaiting Image" can be displayed if there not yet a photo record associated with the product.

I could solve this with a nested query, but that's messy.

NB: Access SQL appreciated.

[DTMark]

I don't have much time, but wanted to try and help - it's something like this: (ISNULL works in SQL Server, not sure about Access):

SELECT [Products].ProductID, [Products].Description, [Products].Price, ISNULL([Photos].[PhotoPath],'AwaitPhoto.jpg') AS [PhotoPath] FROM [Products] LEFT OUTER JOIN [Photos] ON [Photos].[ProductID] = [Products].[ProductID] AND [Photos].[PhotoID] = (SELECT TOP 1 PhotoID FROM [Photos] WHERE [Photos].[ProductID] = [Products].[ProductID])

[r937]

SELECT Pro.ProductID
     , Pro.Description
     , Pro.Price
     , IIF( Pho.PhotoPath IS NULL
          , 'AwaitPhoto.jpg'
          , Pho.PhotoPath ) AS PhotoPath 
  FROM Products as Pro
LEFT OUTER 
  JOIN Photos as Pho
    ON (
       Pho.ProductID = Pro.ProductID 
   AND Pho.PhotoID = 
       ( SELECT MIN(PhotoID)
           FROM Photos 
          WHERE ProductID = Pro.ProductID )
       )

[paroles32]

Try google for a tutorial