complex total count

[CanMan2004]

Hi all

Not sure if anyone can help me, but I have quite a complex query to write and need some help.

Basically I have a table of products called "PRODS", an example table looks like

ID  AREA    TYPE    TITLE              PRICE
1    3        1        IPOD NANO      6y865g
2    5        2        DELL LAPTOP    c4tg85
3    3        1        Carry Case      7uhb54

I then have a table of prices called "PROD_PRICES", an example looks like

ID    CODE      PRICE
1      6y865g    114.76
2      3gg754    23.00
3      c4tg85    566.77
4      7uhb54  234.00

As you will notice, the field "CODE" in the above table ("PROD_PRICES") contains a code which relates to the codes held in the field "CODE" in the "PRODS" table.

What I want to do is to perform a query, which would take all products from the "PRODS" table which has a value of "3" under the field "AREA" and a value of "1" under the field "TYPE", for that query, I would do

[code]SELECT * FROM `prods` WHERE `area`='3' AND `type`='1'[/code]

With the table "PRODS", it would return rows with ID numbers "1" and "3".

What I want to do with the data now, is to take the code held in the "PRICE" field for each row returned and then lookup that code in the table "PROD_PRICES" and return the value "PRICE", but then add them up, so that it gives me a total value.

If I returned rows "1" and "3" from the "PRODS" table, then I would get

ID  AREA    TYPE    TITLE              PRICE
1    3        1        IPOD NANO      6y865g
3    3        1        Carry Case      7uhb54

I would then take the "PRICE" code for each row returned, so row 1 code is "6y865g", so it would look that up in the table "PROD_PRICES" and return

ID    CODE      PRICE
1      6y865g    114.76

and then it would take the code from row 2 "7uhb54" and return

ID    CODE      PRICE
4      7uhb54  234.00

It would then take the 2 values in the "PRICES" field

114.76
and
234.00

and add them up, to make the value

348.76

Does that make sense?

Can anyone help?

Thanks in advance everyone

Dave

[utexas_pjm]

If I'm not oversimplifying your problem this should work:

[code]
SELECT
  SUM(pp.price) as sum
FROM
  `prod_prices` as pp
LEFT JOIN
  `prods` as p
ON (pp.code = p.price)
WHERE p.area='3' AND p.type='1'
[/code]

Best,

Patrick

[ted_chou12]

here... i dont know if it will work or not, but it starts you off
<?php
//get the info from the first table && specify your standards
$result = mysql_query("SELECT * FROM 'prods' WHERE 'area'='3' AND 'type'='1'") or die(mysql_error()); 

//using the while function, putting subs into arrays
while($row = mysql_fetch_array($result)){
$result = mysql_query("SELECT * FROM 'prod_prices' WHERE 'code'='$row['price']'") or die(mysql_error());
$row = mysql_fetch_array($result);
$subtotals[] = $row['price'];}

//add them up to get the total...
$total = 0;
foreach ($subtotals as $st){$total = $total + $st;}
echo "$total\n";?>

[CanMan2004]

thanks, your help has been great