php/mysql help

[c_pattle]

I have two tables.  The first is a list of articles where the primary key in the article number.  The second table is a list of ratings for those article where again the primary key is the article number (so I can link the two).  I'm trying to use a sql command to select all of the information in the articles table along with the average rating for that article. 

 

I have this so far which works however it only gives me the first article and no articles after that.  I think the problem is that the avg(ratings.score) is the average of ALL of the articles rather than for just one. 

 

SELECT AVG(ratings.score), articles.* FROM ratings, articles where ratings.article_number = articles.article_number;

 

Let me know if I didn't explain this well enough.  Thanks for any help. 

[RussellReal]

you could do a sub query

 

like..

 

SELECT (SELECT AVG(ratings.score) FROM ratings WHERE ratings.article_id = articles.id) As average FROM articles;

 

a join will not really work