KddC KddC - 3 months ago 10
MySQL Question

LEFT JOIN only first row

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'

Answer

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