var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: combine 2 queries into 1
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 :
select c_id,c_name from _cats order by c_name
Print the name : c_name
now , i loop in the query and i get articles by this query :
Print article : a_title
select TOP 5 a_title from _articles where a_cat = prevquery.c_id order by a_date desc
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:
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
Originally Posted by
Originally Posted by
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)
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 :
now i need a query to show latest 2 articles for each category
Article 8 | 06 Jun 2008
Article 4 | 03 Jun 2008
Article 7 | 05 Jun 2008
Article 6 | 04 Jun 2008
Article 5 | 03 Jun 2008
Article 3 | 01 Jun 2008
Now it is clear .
FROM _cats AS c
JOIN _articles AS a
ON a.a_cat = c.c_id
AND ( SELECT COUNT(*)
WHERE a_cat = c.c_id
AND a_date > a.a_date ) < 2
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 ?
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?
show create table _articles;
show create table _cats;