mysql - How can I get the first result from a subquery?

Question

I have two tables. Here is a simplified breakdown:

Table #1 - Album:
Rows:
albumId | title | userId

Table #2 - Photo:
Rows:
photoId | src | albumId

I want to get the first photo's src from each album. This is pretty clearly not what I'm looking for but here is what I have:

SELECT pa.id, pa.title, p.src
FROM Album pa
LEFT JOIN Photo p ON pa.Id = p.albumId
WHERE pa.userId = 1

That returns all of the photos from the user. I would like the first result for each album in those results.

Answer 1

I think you may want to add a cover_photo_id, as @zerkms said, but this could do the trick (don't know if using subqueries is efficient enough for your situation)

SELECT pa.albumId, pa.title, p.src
FROM Album pa
LEFT JOIN Photo p 
  ON p.photoId = (SELECT MIN(photoId) FROM Photo WHERE albumId = pa.albumId)
WHERE pa.userId = 1