195

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'
Rick James
  • 135,179
  • 13
  • 127
  • 222
KddC
  • 2,853
  • 2
  • 17
  • 19
  • https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html – Sinux Jun 21 '16 at 03:12
  • here is solution https://stackoverflow.com/a/7588442/612987 – Wasim A. Jan 13 '18 at 16:40
  • 2
    What does "SOLUTION" mean? Is it the desired output? A "solution" belongs in an answer post, not a question post. If it is already in an answer post then mark your question as a duplicate. [Help] [meta] [meta.se] – philipxy May 30 '22 at 08:59

9 Answers9

132

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
Matt Dodge
  • 10,833
  • 7
  • 38
  • 58
  • 1
    Thanks for your fast response. This was not the exact answer, but totally got me on the right way. I always tried to join both on the same level instead of making the one depended from the other. Thank you very much for leading me on the right track. Edited the first post – KddC Mar 25 '13 at 23:39
  • @KddC instead of modifying the question to add the answer, edit this answer or make your own answer. – PhoneixS Aug 12 '15 at 11:47
  • 5
    Wouldn't at this point a simple sub-query be better? Cause now you have a join, and a sub-query. Just asking cause I am looking for solution to same problem :) – galdikas Feb 11 '16 at 10:36
  • Do a sub-query, nested selects can get expensive and ugly. – radtek Mar 09 '17 at 22:29
  • 4
    subquery is too slow. – Sinux Apr 18 '17 at 03:07
  • 2
    @Sinux define "too slow". It depends on number of records and on given requirements. – observer Mar 06 '18 at 00:06
  • 2
    This won't work ! Sub-query does NOT allow passing on field from parent query !!! – Thư Sinh Mar 10 '20 at 07:48
  • @ThưSinh yes, fields are not passed because subquery is still within the FROM clause. fields only accessible if the subquery is put into subsequent WHERE clause. – Darius Jul 31 '20 at 08:24
  • What would then be the exact right answer? – Jovylle Nov 03 '21 at 01:49
59

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
Denis Khvorostin
  • 851
  • 6
  • 12
  • 2
    i don't think this will be the first row(first id or first of something else), it's just randomly choose one among the left join rows. – Sinux Jun 21 '16 at 03:04
  • 35
    This query is wrong. It will select "lowest" artist name not name of first artists in set. – Glapa Aug 12 '16 at 16:54
  • 7
    Wrong for the question…but exactly what I want. In my case, I just want the first ID from the table I'm joining in. – Drew Stephens Feb 08 '17 at 20:42
  • 2
    The problem here is that if you decide to do a COUNT or SUM you will have screwed up data. The problem with Subselect is that it's heavier for getting the data as it created a temporary table... I wish MySql could have a LIMIT on the LEFT JOIN level. – Shadoweb Feb 20 '17 at 11:25
  • 1
    This solution has performance issue. Use Min/Max solution instead. – Sadegh PM Sep 02 '18 at 11:33
  • @Glapa - Tables are, by definition, unordered. That is, there is no "first artist in set". – Rick James Jul 17 '19 at 14:31
  • @DrewStephens - Your question asked for `a.*`; Denis gave you `min(artist_name)`; now you are saying you want only `a.id`. These have _different_ solutions; you should start a new question that specifies what you really want. Meanwhile, Drew's answer does not deserve the upvotes since he answered a different question. – Rick James Jul 17 '19 at 14:38
  • @RickJames there will always be the first in set unless set is empty. Question itself is implying that order should be ascending based on PK AI. But that does not change anything, min on name will do alphabetical comparison. Result won't contain what it should. – Glapa Feb 21 '20 at 18:08
56

@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'
KddC
  • 2,853
  • 2
  • 17
  • 19
17

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

oriadam
  • 7,747
  • 2
  • 50
  • 48
  • notes: it will also work with only `LIMIT` or only `MIN`. the `ON` condition must be on the joined table primary key to avoid performance impact. – oriadam May 13 '20 at 13:34
  • Yes, this solution for MySQL up to 5.6 is what I'm seeking for 10 years and haven't been fearless enough to get it. Great, thank you! – Milda Jun 22 '22 at 17:47
3

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
CyberClaw
  • 435
  • 3
  • 13
2

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!

Ari Waisberg
  • 1,186
  • 1
  • 12
  • 23
  • Assuming there are multiple rows (provinces?) for each `country_code`, that `GROUP BY` is improper. See `ONLY_FULL_GROUP_BY`. – Rick James Jul 17 '19 at 14:53
  • 1
    You don't have to create a view to be used solely for the LEFT/INNER JOIN?! Can use a subquery or a `WITH x AS (` clause? – kiradotee Jan 20 '20 at 14:23
  • For performance reasons please avoid `SELECT *`. Instead, select the exact fields that you need and nothing more. It will allow the optimizer to work better and you will most probably see a performance boost. You can see the difference with `EXPLAIN`. – oriadam Jun 30 '22 at 06:24
  • @oriadam I agree, but here it was just for the example, it didnt make sense to write the fields... – Ari Waisberg Jul 01 '22 at 15:04
1

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.

HoldOffHunger
  • 18,769
  • 10
  • 104
  • 133
  • 3
    The `GROUP_CONCAT` trick is good as long as the number of values is limited. The default limit is 1024 characters. – Rick James Jul 17 '19 at 14:54
  • 1
    Nice hack, but the performance would be lousy in many cases. it takes ALL possible values, concatenate them, then split the first one. also it will break of the name has `"_"` in it so I recommend using some unprintable char as separator. – oriadam Feb 25 '21 at 09:03
1

I know this is not a direct solution but as I've faced this and it's always a huge problem for me, and also using left join select etc. sometimes lead to a heavy process cost in database and server, I prefer doing this kind of left joins using array in php like this:

First get the data in range from second table and while you need just one row from second table, just save them with left join in-common column as key in result array.

SQL1:

$sql = SELECT artist_id FROM feeds_artists fa WHERE fa.feed_id {...RANGE...}
    $res = $mysqli->query($sql);
if ($res->num_rows > 0) {
    while ($row = $res->fetch_assoc()) {
        $join_data[...$KEY...] = $row['artist_id'];
}

Then, get the base data and add detail of left join table from previous array while fetch them like this:

SQL2:

$sql = SELECT * FROM feeds f WHERE f.id {...RANGE...};
$res = $mysqli->query($sql);
if ($res->num_rows > 0) {
    while ($row = $res->fetch_assoc()) {
        $key = $row[in_common_col_value];
        $row['EXTRA_DATA'] = $join_data[$key];
        $final_data[] = $row;
}

Now, you'll have a $final_data array with desire extra data from $join_data array. this usually works good for date range data and like this.

Saghachi
  • 851
  • 11
  • 19
0

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
Didier68
  • 1,027
  • 12
  • 26