Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2010
    Posts
    1

    Exclamation Unanswered: Problem with Many-to-one relationship and Limits

    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 * 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:
    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 unique article ids?

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You can always use subqueries to limit your selection:

    SELECT *
    FROM (SELECT id
    FROM articles
    ORDER BY id DESC
    LIMIT 5) a,
    article_authors aa,
    authors au,
    articles art
    WHERE art.id = a.id
    AND aa.article_id = art.id
    AND au.id = aa.author_id;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •