From: Raghuveer Rawat (techhelp07gmail.com)
Date: Wed Jan 23 2008 - 11:14:08 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]