Main Category page to show all categories and the last three articles under each...

[jturmel]

Basically what I have here is a simple real estate articles section on my website and it's been so long since I've been in the web development world that I'm having a rough time remembering some stuff.

What I have is a categories table and an articles table. On the index page for the categories, I want it to list out all the categories with the 3 most recent articles that belong under each category.... just having a rough time, I created a query inner joining the two tables however if I just add LIMIT 3, it limits the overall total to 3, I want it to just limit 3 articles from each...

Any help is greatly appreciated.

Thanks.

-J

[r937]

select C.name  as category
     , A1.name as article
     , A1.articledate
  from categories as C
inner
  join articles as A1
    on C.id = A1.category_id
inner
  join articles as A2
    on C.id = A2.category_id
   and A1.articledate <= A.articledate
group 
    by C.name 
     , A1.name 
     , A1.articledate
having count(*) <= 3    
order 
    by C.name  
     , A1.name 
     , A1.articledate desc

[jturmel]

You are THE man!

Thanks a bunch!

-J