mysql - How can I get the first result from a subquery?
mysql, sql
Solution
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
Problem
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.