LEFT JOIN仅第一行 [英] 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 | ...

艺术家

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'

仅获取feeds_artists的第一行，但已经行不通了.

just to get only the first row of the feeds_artists, but already this does not work.

由于我的数据库，我不能使用TOP，我无法按feeds_artists.artist_id分组结果，因为我需要按日期对它们进行排序(我通过这种方式对结果进行分组，但是结果不在其中最新)

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)

也尝试了OUTER APPLY的尝试-也没有成功. 老实说，我无法想象这些行中发生了什么，这可能是我无法使它起作用的最大原因.

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.

解决方案:

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'

推荐答案

@Matt Dodges答案使我走上了正确的轨道.再次感谢您提供所有答案，在此期间，这对很多人都有所帮助.像这样工作了:

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

这篇关于LEFT JOIN仅第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！