LEFT JOIN only first row

Question

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

User · 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

User · Answer

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

User · Answer

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

User · Answer

I want to give a more generalized answer   One that will handle any case when you want to select only the first item in a LEFT JOIN   You can use a subquery that GROUP CONCATS what you want  sorted  too    then just split the GROUP CONCAT d result and take only its first item  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 our ORDER BY option  this will return a Person id for someone like  Zack    If we wanted someone with the name like  Andy   we would change ORDER BY FirstName DESC to ORDER BY FirstName ASC   This is nimble  as this places the power of ordering totally within your hands   But  after much testing  it will not scale well in a situation with lots of users and lots of data   It is  however  useful in running data-intensive reports for admin

User · Answer

For some database like DB2 and PostgreSQL  you have to use the key word LATERAL for specifying a sub query in the LEFT JOIN    here  it s for DB2  SELECT f    a   FROM feeds f LEFT JOIN LATERAL         SELECT artist id  feed id     FROM feeds artists sfa     WHERE sfa feed id   f id     fetch first 1 rows only   fa ON fa feed id   f id LEFT JOIN artists a ON a artist id   fa artist id

User · Answer

I ve used something else  I think better     and want to share it   I created a 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 yet  that I think that we need to do this solution BECAUSE WE DID SOMETHING WRONG IN THE ANALYSIS    at least in my case    but sometimes it s cheaper to do this that to redesign everything     I hope it helps

User · Answer

Here is my answer using the group by clause  SELECT   FROM feeds f LEFT JOIN        SELECT artist id  feed id     FROM feeds artists     GROUP BY artist id  feed id    fa ON fa feed id   f id LEFT JOIN artists a ON a artist id   fa artist id

User · Answer

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

[mysql] LEFT JOIN only first row

Examples related to mysql

Examples related to join

Examples related to left-join

Examples related to groupwise-maximum