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_____'[email protected]'_________'this is content'_________11.03.2010_____NULL_____
2_____'[email protected]'_____'this is last comment'____01.03.2010_____1_____
3_____'[email protected]'_____'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_____'[email protected]'_____'this is content'[email protected]___________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.
