Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email email@example.com
From: Raghuveer Rawat (techhelp07gmail.com)
Date: Wed Jan 23 2008 - 11:14:08 CST
I need some urgent for sql query.. It will be great if someone could help
I have ARTICLE, FAVORITE_ARTICLES, RATING Tables apart from other table
USER, CHANNEL, CATEGORY etc....
ARTICLE table stores a user's article, FAVORITE_ARTICLES will store a user's
favorite articles, and rating table ratings on a article..
I have a requirement to fetch All article by descending order of their
ratings and I have to find favorite articles count for each articles as
well. My SQL works fine if I put outer join with Article and Favorite
Articles table or between Article and Ratings table.. but it doesn't work if
put Outer with both Favorite Articles and Rating Table....
How can resolve this issue? How can I join use Outer Join with Two Tables in
a SQL... If it is not possible then What is the alternate ways?
I tried with below SQL....
SELECT a.CHANNEL_ID, ch.CHANNEL_NAME, a.CATEGORY_ID, ca.CATEGORY_NAME,
a.LAST_UPDATED_DATE, a.ARTICLE_ID, a.USER_ID, u.USER_NAME as WRITER_NAME,
a.DEDICATED, a.ARTICLE_LENGTH, a.ARTICLE_DESC, a.READ_COUNT,
count(fa.ARTICLE_ID) as myFavArticlesCnt, avg(rating),
COUNT(r.ARTICLE_ID) AS RATINGS
FROM article a left join favorite_articles fa using (article_id), ARTICLE b
left join RATING r using (article_id), USER u, CHANNEL ch,CATEGORY ca
WHERE a.CHANNEL_ID = '1' AND a.CATEGORY_ID = '1' AND a.CHANNEL_ID =
AND a.CATEGORY_ID = ca.CATEGORY_ID and a.USER_ID = u.USER_ID AND
a.PUBLISH_FLAG = '1'
AND a.IS_DELETE = '0' AND A.last_updated_date >= Date_SUB(Now(), INTERVAL
GROUP BY a.ARTICLE_ID ORDER BY RATINGS DESC