Hi,

I am kinda stumped on this one... I am using an Access database to store (filepaths to) pictures as well as comments people can leave on them. One picture can have multiple comments, so the tables look like this:
Code:
_____________      _____________
|Photos      |    |Comments     |
|------------|    |-------------|
|Id (PK)     |    |Id (PK)      | 
|PhotoPath   |    |PhotoId (FK) |
|Description |    |Comment      |
|____________|    |_____________|
What I need to do now is retrieve a list of all Photos along with their Comments. So I have a class Photo with a collection of Comments:
vb.net Code:
  1. Public Class Photo
  2.    
  3.     Public Property Id As Integer
  4.     Public Property PhotoPath As String
  5.     Public Property Description As String
  6.     
  7.     Public Property Comments As List(Of Comment)
  8.  
  9. End Class
  10.  
  11. Public Class Comment
  12.   
  13.     Public Property Id As Integer
  14.     Public Property PhotoId As Integer
  15.     Public Property Comment As String
  16.     
  17. End Class

I can retrieve the records using an INNER JOIN query (right?), as such;
Code:
SELECT Photos.*, Comments.*
FROM (Photos INNER JOIN Comments ON Photos.Id = Comments.PhotoId)
From the result of that, for each row in the result I can create a new Photo instance, set its properties and put it in a List(Of Photo) for further processing (displaying). However, how do I handle the Comments? With this query I would get a lot of duplicate Photos. I'd get a row for each comment, but most of these rows will represent the same photos...

How do I handle this the best way? I have used inner join queries before, but only for a one-to-one relationship so I never had this problem before. Now when I finally need a one-to-many relationship I realize I've no idea how to handle this appropriately...

There must be some standard way of handling this?


When I previously handled cases like this I didn't use an inner join, I just retrieved all Photos first, then iterate through them and run another query to retrieve the Comments that belong to that Photo (SELECT * FROM Comments WHERE PhotoId = ?). That works fine, but when the tables are getting larger it is getting noticeably slower. So I want to do it in a single query, I definitely want to avoid running hundreds of queries if I can

Thanks!