Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2008
    Posts
    17

    Question Unanswered: combine 2 queries into 1

    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 :
    Code:
    select c_id,c_name from _cats order by c_name
    loooooop

    Print the name : c_name

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

    Code:
    select TOP 5 a_title from _articles where a_cat = prevquery.c_id order by a_date desc
    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 !

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you tried the SQL Server solution provided here: http://www.dbforums.com/showthread.php?t=1635655 ?
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2008
    Posts
    17
    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 !

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh, it must have been this that threw me off
    Quote Originally Posted by georgev
    What version of SQL Server?
    Quote Originally Posted by dicanio
    georgev , it is ms sql 2000.
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2008
    Posts
    17
    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 ?


  6. #6
    Join Date
    Nov 2008
    Posts
    17
    i don't think there is no solution for my problem , but i don't know why experts doesn't discuss about it.

  7. #7
    Join Date
    Nov 2008
    Posts
    17
    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 .

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT c.c_name
         , a.a_id
         , a.a_title
         , a.a_date
      FROM _cats AS c
    INNER
      JOIN _articles AS a
        ON a.a_cat = c.c_id
       AND ( SELECT COUNT(*) 
               FROM _articles  
              WHERE a_cat = c.c_id
                AND a_date > a.a_date ) < 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2008
    Posts
    17
    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 ?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    gots indexes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2008
    Posts
    17
    yes , foreign keys have normal index and a primary and unique index for key fields.
    maybe you suggest any index ?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do any of those indexes include the date column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I'm sure if we all wait long enough he'll eventually give us the actual indexes and then we can answer his question.

  14. #14
    Join Date
    Nov 2008
    Posts
    17
    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

  15. #15
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Would it be possible to run the following MySQL commands to get a better picture?
    Code:
    show create table _articles;
    show create table _cats;
    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •