SQL Query from Hell...

The_Chancer

Trying to get to grips with this query... and failing madly...

I am trying to get the total number of each product in a table, where the order number is taken from a different table.. like so..

Table 1

orderid | placed | paid
1 | no | no
2 | yes | yes
3 | yes | yes

Table 2

orderid | productnumber | quantity
1 | 2 | 2
2 | 2 | 4
2 | 3 | 1
3 | 3 | 2
3 | 1 | 9

From here we do this...

$query=mysql_query("SELECT orderid from table_1 where placed=yes and paid=yes");
//this would give us 2 & 3 as the orderid

Now I need to know, preferably in one statement, how to get the answer from table2 to give me

Product 1 - quantity 9
Product 2 - quantity 4
Product 3 - quantity 3

But I do not know where to start.... any ideas would be a treat..

Thanks,

The_Chancer

OK - using the INNER JOIN statement as follows:

$query=do_query("SELECT product,quantity,supplier FROM shopping_cart INNER JOIN orders ON shopping_cart.session = orders.cartid WHERE orders.order_placed='yes'");

Gives me almost what I need.

But is there a way of giving a total for the quantity column to give the answers as above ?

Distinct and Sum I think will feature somewhere in it, but I am not sure where....

The_Chancer

$query=mysql_query("SELECT product as product,SUM(quantity) as quantity, supplier as supplier FROM shopping_cart INNER JOIN orders ON shopping_cart.session = orders.cartid WHERE orders.order_placed='yes' GROUP BY shopping_cart.product");
while($product=mysql_fetch_array($query))
{
//code go here
}

Works perfectly...