query from 2 tables

________

Hi,
I got a problem with a query. I got two tables, one with product information and one with customer rating of products.

Product information (name of table: prod)
| id | name | price |

Customer rating (name of table: rating)
| id | prod | value

The problem is that I need a query that select's all the info from "prod" and SUM the value, if any, from "rating" where rating.prod = prod.id

How do I make a query like that... it is important that i can add an or more extra where's to the query... like:
where... and id='2' and so on!

😕 Need all the help I can get!😕

Thanks!

nnichols

Try -

SELECT *, SUM(r.value), AVG(r.value), COUNT(r.value)
FROM prod p, rating r
WHERE p.id=r.prod
GROUP BY p.id

just added the COUNT() and AVG() for example

________

The problem is that I only get the products that have been rated... all the products that does not have a value in the "rating" table are not retrived!

What Im looking for is a query that give me an output like this:

prod.id | prod.name | prod.price | rating of the product (if any)|

It lists all the products in the the "prod" table and SUM the value of the grades where prod.id = rating.prod

Is that possible to do in one query?😕

tsinka

Maybe the last post on this thread might help you 🙂

http://www.phpbuilder.com/board/showthread.php?s=&postid=10350932#post10350932

nnichols

Sorry. Wasn't thinking straight -

SELECT *, SUM(r.value), AVG(r.value), COUNT(r.value)
FROM prod p
LEFT JOIN rating r ON p.id=r.prod
GROUP BY p.id

________

That worked!
Thanks a lot!