Hi, I need some urgent for sql query.. It will be great if someone could help me.. 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.TITLE, 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 = ch.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 30 DAY) GROUP BY a.ARTICLE_ID ORDER BY RATINGS DESC Thanks Raghu
