[RESOLVED] Help with query - counting from another table

[Hen Asraf]

I have this query, but it doesn't seem to work:
SELECT `articles`.* FROM `articles`,`article_comments`
WHERE `article_comments`.`article` = `articles`.`id`
ORDER BY COUNT(`article_comments`.`article`) LIMIT 5
What I need to do is order the articles by the amount of comments they have.

I also have this query,
SELECT `articles`.* FROM `articles`,`ratings`
WHERE `ratings`.`article` = `articles`.`id`
ORDER BY AVG(`ratings`.`score`) LIMIT 5
Which is supposed to order articles by their ratings.

Help? ):

[Hen Asraf]

Bump? D:

[chazzy]

need a bit more info, like what version of mysql you're using.

but in general a query like this should work:


select
a.*,
c.art_cnt
from articles a,
(select count(*) AS art_cnt from article_comments ac where ac.article = a.id) c
order by art_cnt limit 5


do something similar for the ratings.

[Hen Asraf]

Well I tried this...SELECT `articles`.*,`ratings`.`avg`
FROM `articles`,(SELECT AVG(`ratings`.`score`) FROM `ratings` WHERE `ratings`.`article` = `articles`.`id` AS `avg`)
ORDER BY `ratings`.`avg` LIMIT 5But it didn't work. As for MySQL version, I'm using 5.1.33.

[chazzy]

that doesn't look like my query at all.


select
a.*,
r.avg_rating
from articles a,
(select avg(rating_column) AS avg_rating from article_ratings ar where ar.article = a.id) r
order by avg_rating limit 5

[Hen Asraf]

Damnit >< you keep changing my table/field names and I can't get it right.
SELECT
`a`.*,
`r`.`avg`
FROM `articles` `a`
(SELECT AVG(`score`) AS `avg` FROM `ratings` `ar` WHERE `ar`.`article` = `a`.`id`) `r`
ORDER BY `avg` LIMIT 5

[Hen Asraf]

Ok, so I tried this and no errors, but it counts the average of ALL the ratings, not only the ones I need...SELECT `articles`.*, `calculated_ratings`.`avg`
FROM `articles`, (
SELECT AVG(`ratings`.`score`) AS `avg`
FROM `ratings`,`articles`
WHERE `ratings`.`article` = `articles`.`id`
) AS `calculated_ratings`
ORDER BY `avg` LIMIT 5

[b4web]

I think that you need to use the "Group By" clause and alias names. I don't know if the following sql is right or not because I don't have time to setup tables to test this, but I'm thinking along these lines:


select c.art_id, a.art_title, c.count(*) AS comment_ct from articletbl a,

commentstbl c
where a.art_id=c.art_id
group by c.art_id
order by comment_ct;

select AVG(c.rating) AS avg_rating, a.art_title from articles a, comments c
where a.art_id=c.art_id
group by a.art_title
order by avg_rating;

[chazzy]

no. don't do a join in your subselect.


SELECT `articles`.*, `calculated_ratings`.`avg`
FROM `articles`, (
SELECT AVG(`score`) AS `avg`,
`article`
FROM `ratings` group by `article` ) AS `calculated_ratings`
WHERE `calculated_ratings`.`article` = `articles`.`id`
ORDER BY `avg` LIMIT 5


sorry missed the group by earlier.

So the first query should really be


select
a.*,
c.art_cnt
from articles a,
(select count(*) AS art_cnt, article from article_comments ac group by article) c
where c.article = a.id
order by art_cnt limit 5

[Hen Asraf]

Finally! Got it working. Thanks everyone! The codes that worked to those interested, are:
SELECT `articles`.*,`calculated_ratings`.`avg`
FROM `articles`, (
SELECT AVG(`score`) AS `avg`, `article`
FROM `ratings`
GROUP BY `article`
) AS `calculated_ratings`
WHERE `calculated_ratings`.`article` = `articles`.`id`
ORDER BY `avg` DESC
LIMIT 5
SELECT `articles`.*, `calculated_comments`.`count`
FROM `articles`, (
SELECT COUNT(`article`) AS `count`, `article`
FROM `article_comments`
GROUP BY `article`
) AS `calculated_comments`
WHERE `calculated_comments`.`article` = `articles`.`id`
ORDER BY `count` DESC
LIMIT 5