LEFT JOIN only first row

Solution 1:

If you can assume that artist IDs increment over time, then the MIN(artist_id) will be the earliest.

So try something like this (untested...)

SELECT *
  FROM feeds f
  LEFT JOIN artists a ON a.artist_id = (
    SELECT
      MIN(fa.artist_id) a_id
    FROM feeds_artists fa 
    WHERE fa.feed_id = f.feed_id
  ) a

Solution 2:

Version without subselect:

   SELECT f.title,
          f.content,
          MIN(a.artist_name) artist_name
     FROM feeds f
LEFT JOIN feeds_artists fa ON fa.feed_id = f.id
LEFT JOIN artists a ON fa.artist_id = a.artist_id
 GROUP BY f.id

Solution 3:

@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'

Solution 4:

based on several answers here, i found something that worked for me and i wanted to generalize and explain what's going on.

convert:

LEFT JOIN table2 t2 ON (t2.thing = t1.thing)

to:

LEFT JOIN table2 t2 ON (t2.p_key = (SELECT MIN(t2_.p_key) 
    FROM table2 t2_ WHERE (t2_.thing = t1.thing) LIMIT 1))

the condition that connects t1 and t2 is moved from the ON and into the inner query WHERE. the MIN(primary key) or LIMIT 1 makes sure that only 1 row is returned by the inner query.

after selecting one specific row we need to tell the ON which row it is. that's why the ON is comparing the primary key of the joined tabled.

you can play with the inner query (i.e. order+limit) but it must return one primary key of the desired row that will tell the ON the exact row to join.

Update - for MySQL 5.7+

another option relevant to MySQL 5.7+ is to use ANY_VALUE+GROUP BY. it will select an artist name that is not necessarily the first one.

SELECT feeds.*,ANY_VALUE(feeds_artists.name) artist_name
    FROM feeds 
    LEFT JOIN feeds_artists ON feeds.id = feeds_artists.feed_id 
GROUP BY feeds.id

more info about ANY_VALUE: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html