Limiting based on value selected from another table

I have the following tables structure:

Category: id, name, limit

开发者_JAVA百科posts: id, category, title, body, date, author

What I am trying to do is showing the latest posts from each category limited by the "limit" field in the category table, ordered by the date of the posts.

Try something like this: (Doesnt test for errors)

$result = mysql_fetch_array(mysql_query("<query to select your category>"));
$limit = $result['limit'];
$queryResult = mysql_query("select * from posts order by date desc limit $limit");
//do whatever you want with the $queryResult

If I understand you:

SELECT * FROM posts LEFT JOIN Category ON posts.category = Category.id WHERE limit = 'some limit' ORDER BY category, date DESC

(Asuming: posts.category is a reference the Primary Key of Category table)

Interesting variation on the N-Greatest-Per-Group

SELECT id, name, limit, postid, title, body, date, author
FROM
(
    SELECT 
        c.id, c.name, c.limit, p.id postid, p.title, p.body, p.date, p.author
        @r:=case when @g=C.id then @r+1 else 1 end r,
        @g:=C.id
    FROM 
       (select @g:=null,@r:=0) n
       CROSS JOIN CATEGORY C
       INNER JOIN POSTS p on p.category = c.id
    ORDER BY 
        C.ID, postid
) X
WHERE r<=c.limit
ORDER BY ID, postid

The side affecting variables essentially produce 2 columns

• @G = group
• @R = row number within the group

This could be expensive because there is no shortcut, it numbers EVERY row in the category/post JOIN, and only after that does it get cut down in the WHERE r<=c.limit bit.