Hi,

i have 2 tables , a table for my articles and a table for categories !

i need to show 5 latest posts in articles for each cat in a page

for now i get cat names by this query :
loooooop

Print the name : c_name

now , i loop in the query and i get articles by this query :

Print article : a_title

end looooop

now i need to combine these 2 queries in one query .

i tried to combine group by and LIMIT but no results , any suggestion ?
also i cannot use LIMIT in subquery ! the i am complicated !

Have you tried the SQL Server solution provided here: prevent loop ?

georgev , yes , the sql server solution may work but i asked this question by mistake in mssql server section , and i found the solution in mssql server , we are developing the application in mssl and mysql for asp .net and php , but that solution doesn't work in mysql cause in mysql we cannot limit the returned rows in a sub query !

now i asked for a solution that works in mysql too !

Oh, it must have been this that threw me off

yes georgev, it was MSSQL server thread and here is my sql thread.
why dont you stop referencing threads and let me know what do you think about the way i can combine these queries in mysql ?

i don't think there is no solution for my problem , but i don't know why experts doesn't discuss about it.

it seems i couldn't make you understand what i am really looking for , then
let me ask you something like this

Table1 :_articles (a_id,a_cat,a_title,a_text,a_date)
Table2 :_cats(c_id,c_name)

Some Data For Table 1 :
=======================================
1 | 1 | Article 1 | Some Text1 | 01 Jun 2008
2 | 3 | Article 2 | Some Text2 | 01 Jun 2008
3 | 3 | Article 3 | Some Text3 | 01 Jun 2008
4 | 1 | Article 4 | Some Text4 | 03 Jun 2008
5 | 3 | Article 5 | Some Text5 | 03 Jun 2008
6 | 2 | Article 6 | Some Text6 | 04 Jun 2008
7 | 2 | Article 7 | Some Text7 | 05 Jun 2008
8 | 1 | Article 8 | Some Text8 | 06 Jun 2008

Some Data For Table 2 :
===================================
1 Sport
2 Math
3 History

now i need a query to show latest 2 articles for each category

Like :

Sport Cat
================
Article 8 | 06 Jun 2008
Article 4 | 03 Jun 2008

Math Cat
================
Article 7 | 05 Jun 2008
Article 6 | 04 Jun 2008

History Cat
================
Article 5 | 03 Jun 2008
Article 3 | 01 Jun 2008

Now it is clear .

yes , this query works but it is too slow for my tables about 1500 rows of articles and 12 rows of cats. (about 2 Secs And 266 Ms).
i am sure it will about 100000 in next months...

is it possible to tune it for better performance ?

gots indexes?

yes , foreign keys have normal index and a primary and unique index for key fields.
maybe you suggest any index ?

do any of those indexes include the date column?

I'm sure if we all wait long enough he'll eventually give us the actual indexes and then we can answer his question.

here is the indexes :

for articles table :
a_id : a normal index and a unique index and it is the primary key field
a_title : a full-text index
a_date : a normal index
a_cat : a normal index

for cats table :
c_id : a normal index and a unique index and it is the primary key field
c_name : no index

Would it be possible to run the following MySQL commands to get a better picture?
Mike