select max from another table

[kibblerok]

hi all, I have two databases, one with details of a product and another with availability and prices in another table with multiple rows per product.

I wish to select the details of each from the details table but also the min and max price of each for each product from the other table, i have tried the code below but it gives me an error #1140 about mixing group columns

SELECT p.product_id, max(a.availability_price), min(a.availability_price)
FROM products p, availability a
WHERE a.product_id = p.product_id

any idea where to go from here?

[lilleman]

Hi,

You must add a GROUP BY clause.

SELECT p.product_id, max(a.availability_price), min(a.availability_price)
FROM products p, availability a
WHERE a.product_id = p.product_id
GROUP BY p.product_id

Yours, Erik.

[kibblerok]

thanks erik

It was so simple, yet I didnt know where to start or what to apply it to

cheers