One-to-Many relationship and limiting the results

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
joshmaker
Forum Commoner
Posts: 25
Joined: Mon May 15, 2006 2:53 pm
Location: Arlington VA

One-to-Many relationship and limiting the results

Post by joshmaker »

I'm having a problem with a many-to-one relationships in a MySQL DB that I am trying to query.

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_id
This way I select one row for each article and author and get a table like this (extraneous fields removed for clarity):

Code: 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 	John
Since IDs are unique it is a simple operation to use PHP to convert these results into four different articles with one of the articles having three different authors.

Where 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?
User avatar
Sofw_Arch_Dev
Forum Commoner
Posts: 60
Joined: Tue Mar 16, 2010 4:06 pm
Location: San Francisco, California, US

Re: One-to-Many relationship and limiting the results

Post by Sofw_Arch_Dev »

Hello Joshmaker,

so to clarify, you want to select the n most recent articles by an article's authors?
Post Reply