Help with Query

[zackiv31]

I'm no SQL pro, but I have the following statement that works... but I want to add something else to it.

SELECT p.id,p.user,p.userid,p.cat,p.storecat,p.ismature,p.title,p.bigimage,p.height,p.width,u.location FROM pp_photos p, pp_users u WHERE p.approved=1 AND p.storecat=0 AND p.userid=u.userid ORDER BY date DESC LIMIT 5;

I have another table, comments, that may or may not have a list of comments related to the p.id of the photos table. something like c.photo=p.id. I want to get the average of the ratings in those comments if they exist. How can I manipulate the above query to get what I want?

I've tried something like this to no avail:

?SELECT p.id,p.user,p.userid,p.cat,p.storecat,p.ismature,p.title,p.bigimage,p.height,p.width,u.location,avg(c.rating) AS prating FROM pp_photos p, pp_users u LEFT JOIN pp_comments c ON (c.photo=p.id) WHERE p.approved=1 AND p.storecat=0 AND p.userid=u.userid ORDER BY date DESC LIMIT 5;
ERROR 1054 (42S22): Unknown column 'p.id' in 'on clause'

And before you ask, yes pp_photos has a field 'id'.. I just dont think I'm formulating it correctly.

 

[DonQuichote]

FROM pp_photos p, pp_users u LEFT JOIN pp_comments c ON ...

Sorry, but if you use the two syntaxes mixed up (comma syntax and JOIN keyword syntax), I would not know what you mean by it. MySQL probably doesn't either. Does it help to rework the query to either one of them?

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[r937]

well, you couldn't rework it using comma syntax, since it requires an outer joiun

therefore, you have to rework it using JOIN syntax

change this --

FROM pp_photos p, pp_users u LEFT JOIN pp_comments c ON (c.photo=p.id) WHERE p.approved=1 AND p.storecat=0 AND p.userid=u.userid

to this --

  FROM pp_photos AS p
INNER
  JOIN pp_users AS u 
    ON u.userid  = p.userid
LEFT OUTER
  JOIN pp_comments AS c 
    ON c.photo = p.id 
 WHERE p.approved = 1 
   AND p.storecat = 0

r937.com | rudy.ca