Problem with join?

[Roybut]

Hi,

I need to retrieve the max and min values of the price field in my db. I have a item table and a category table. I am using $_GET to get the category name and then I need to retrieve the min and maxvalues only for the items in that particular category.

I can get the max value of all items with this:

$maxvalue = "Select max(price) from item";
$resultmax = mysql_query($maxvalue);
$rowmax = mysql_fetch_array($resultmax);
echo $rowmax['max(price)'];

but when I try to join the tables to get the item with that value for a particular category:

$minvalue = mysql_query("Select i.min(price) from item i
			 left join category c
			 on i.cat_id = c.cat_id
			 WHERE c.cat_name = '".$_GET['category']."' and i.visible = 1");
$rowmin = mysql_fetch_array($minvalue);
echo $rowmin['min(price)'];

it doesn't work.

Any ideas?

Thanks.

[ReginaldoRigo]

Look at this. I guess that solves your problem.

create table item (
cod int,
price numeric(15,2))

create table category (
cod int,
dscategory varchar(20))


select a.cod, a.price, b.dscategory from
item a left join category b
on a.cod = b.cod
where a.price = ( select max( price ) from item c 
where a.cod = c.cod )

Reginaldo

[Roybut]

Hi Reginaldo,

Thanks for the help. I tried it, obviously changing it a bit to fit my db. I ended up with this:

$minvalue = "SELECT i.price, i.cat_id, c.cat_name
	    	FROM item i left join category c
	    	left join category c
	    	on i.cat_id = c.cat_id
	    	where i.price = (SELECT max(price)
	    					 FROM item ii
	    					 WHERE i.cat_id = ii.cat_id)";

$resultmin = mysql_query($minvalue);
$rowmin = mysql_result($resultmin);
echo $rowmin['max(price)'];

but it doesn't work.

I finally got the data by doing this (although I know it is messy):

$cat_id = "SELECT cat_id
		   FROM category
		   WHERE cat_name = '" . $_GET['category'] . "'";
$cat_result = mysql_query($cat_id);
$the_cat = mysql_fetch_array($cat_result);
$cat = $the_cat[0];
echo $cat;
echo "<br>";



$maxvalue = "Select max(price) from item where cat_id = $cat";
$resultmax = mysql_query($maxvalue);
$rowmax = mysql_fetch_array($resultmax);
echo $rowmax['max(price)'];

Cheers,

Roy