I am working with three tables in a CMS (written by someone else and not easily modified). The first table lists articles, the second table lists authors and the third table maps authors to articles such that each article can have an unlimited number of authors.
When I need to select articles I can do so like this:
Code: Select all
SELECT * FROM articles
JOIN article_authors ON article_authors.article_id = articles.id
JOIN authors ON authors.id = article_authors.author_idCode: Select all
ID ARTICLE AUTHOR
1 My Article John
2 My Second Article Sally
3 My Third Article Sally
3 My Third Article Bob
3 My Third Article Jim
4 My Fourth Article JohnWhere I run into problems is on pages where I need to impose a limit on the number of articles I select. I don't know of an easy / efficient way to select the three most recent articles and all their authors.
If I impose a LIMIT on the number of rows that I select the resulting table will either truncate some of the articles or authors that I want to select.
Can someone help me find an efficient way to limit my selection to include as many rows as necessary but only three (or however many) unique article ids?