LEFT JOIN 仅第一行
我阅读了很多关于只获取左连接的第一行的帖子,但是由于某种原因,这对我不起作用.
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)
供稿
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'
相关文章