LEFT JOIN only the first line

Question

I read a lot of threads about getting only the first line of the left join, but for some reason this does not work for me.

Here is my structure (simplified, of course)

Channels

id | title | content ---------------------- 1 | Feed 1 | ... 

Painters

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

to get only the first line of feeds_artists, but that already doesn't work.

I cannot use TOP because of my database, and I cannot group the results by feeds_artists.artist_id , because I need to sort them by date (I got the results by grouping them this way, but the results are newest)

I tried something with OUTER APPLY, and also did not succeed. Honestly, I cannot imagine what is happening in these ranks - perhaps the biggest reason why I cannot get this to work.

DECISION:

 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 1

@Matt Dodges answer put me on the right track. Thanks again for all the answers that helped many guys. It turned out 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' 

Answer 2

If you can assume that artist identifiers increase over time, then 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 

Answer 3

Version without subquery:

  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 

Answer 4

Version without a subtitle, which will join only the first author:

 SELECT f.title, f.content, a.artist_name artist_name, min(a.id) as m 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 HAVING a.id = m; 

Answer 5

I used something else (I think it’s better ...) and want to share this:

I created VIEW that has a "group" clause

 CREATE VIEW vCountries AS SELECT * PROVINCES GROUP BY country_code SELECT * FROM client INNER JOIN vCountries on client_province = province_id 

I want to say more that I think we need to make this decision, BECAUSE WE SOMETHING IS WRONG AT AN ANALYSIS ... at least in my case ... but sometimes it’s cheaper to do to redesign everything ...

I hope this helps!

Answer 6

I want to give a more generalized answer. One that will handle any case where you want to select only the first element in the left join.

You can use the subquery that GROUP_CONCATS what you want (sorted too!), And then just split the result of GROUP_CONCAT and take only its first element, like so ...

 LEFT JOIN Person ON Person.id = ( SELECT SUBSTRING_INDEX( GROUP_CONCAT(FirstName ORDER BY FirstName DESC SEPARATOR "_" ), '_', 1) ) FROM Person ); 

Since we have DESC as an ORDER BY option, the person identifier for someone like "Zack" will be returned. If we wanted someone with a name like Andy, we would change ORDER BY FirstName DESC to ORDER BY FirstName ASC.

This is adroit, as it provides the ability to streamline completely in your hands. But after a lot of testing, it will not scale well in a situation with a large number of users and a lot of data.

This, however, is useful when running reports with heavy data usage for the administrator.