LEFT JOIN only first row
groupwise-maximum, join, left-join, mysql
Solution
@Matt Dodges answer put me on the right track. Thanks again for all the answers, which helped a lot of guys in the mean time. Got it working like this:
SELECT *
FROM feeds f
LEFT JOIN artists a ON a.artist_id = (
SELECT artist_id
FROM feeds_artists fa
WHERE fa.feed_id = f.id
LIMIT 1
)
WHERE f.id = '13815'
Problem
I read many threads about getting only the first row of a left join, but, for some reason, this does not work for me. Here is my structure (simplified of course) Feeds ``` id | title | content ---------------------- 1 | Feed 1 | ... ``` Artists ``` artist_id | artist_name ----------------------- 1 | Artist 1 2 | Artist 2 ``` feeds_artists ``` rel_id | artist_id | feed_id ---------------------------- 1 | 1 | 1 2 | 2 | 1 ... ``` Now i want to get the articles and join only the first Artist and I thought of something like this: ``` SELECT * FROM feeds LEFT JOIN feeds_artists ON wp_feeds.id = ( SELECT feeds_artists.feed_id FROM feeds_artists WHERE feeds_artists.feed_id = feeds.id LIMIT 1 ) WHERE feeds.id = '13815' ``` just to get only the first row of the feeds_artists, but already this does not work. I can not use `TOP` because of my database and I can't group the results by `feeds_artists.artist_id` as i need to sort them by date (I got results by grouping them this way, but the results where not the newest) Tried something with OUTER APPLY as well - no success as well. To be honest i can not really imagine whats going on in those rows - probably the biggest reason why i cant get this to work. SOLUTION: ``` SELECT * FROM feeds f LEFT JOIN artists a ON a.artist_id = ( SELECT artist_id FROM feeds_artists fa WHERE fa.feed_id = f.id LIMIT 1 ) WHERE f.id = '13815' ```