Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2010
    Posts
    1

    Unanswered: count, max and merge two tables(sql query?)

    hi, i do not know if my table structure is true. i have an article table like below:

    Code:
    ARTICLES
    article_id (primary)
    author
    content
    date
    parent_article_id
    
    ARTICLE_DETAIL
    article_id
    viewing_count

    sample data

    Code:
    ARTICLES
    id____author_____________________content________________date_______parent_article_id
    
    1_____'test@test.com'_________'this is content'_________11.03.2010_____NULL_____        
    2_____'comment1@test.com'_____'this is last comment'____01.03.2010_____1_____        
    3_____'comment2@test.com'_____'this is first comment'___11.02.2010_____1_____       
    
    
    ARTICLE_DETAIL
    id___viewing_count
    1_____555


    i want to get articles and date and name of last commentator. and viewing count also.

    Code:
    DESTINATION TABLE
    id___________author_______article_content_______article_date___last_commentator_email_____last_commentator_date____viewing_count
    
    1_____'test@test.com'_____'this is content'_____11.03.2010_____comment1@test.com___________01.03.2010_______________555

    what should the "sql query" be?
    my db is access.

    my query is below but not working:
    Code:
    SELECT
    articles1.id,
    articles1.author,
    articles1.content,
    articles1.date,
    COUNT(articles2.id) AS answer_count,
    MAX(articles2.date) AS last_message_date,
    article_detail.viewing_count 
    FROM article article1 
    LEFT OUTER JOIN article article2 ON article1.id = article2.parent_article_id
    LEFT OUTER JOIN article_detail ON article1.id = article_detail.id 
    WHERE articles.parent_article_id IS NULL
    GROUP BY
    articles1.id,
    articles1.author,
    articles1.content,
    articles1.date,
    article_detail.viewing_count
    regards.

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    You should break the comments off into a separate table, it will make life much much easier.

Posting Permissions

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