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.