join and subquery

[saravanataee]

Dear all,
I am having the following problem and did not know the solution.

I have three related tables.

product
purchase_order
git_order.

In product table

product_id product_name
1 barrings
2 wheel rim
3 rodd

purchase_order

po_id product_id package_id numbers received etc.
1 1 2 100 50
2 2 4 250 60
3 1 1 200 40
4 1 3 200 40
5 2 5 200 40
6 1 1 200 40
7 1 5 200 40


git_order
git_id product_id package_id numbers received etc.
1 1 2 100 50
2 2 4 250 60
3 1 1 200 40
4 1 3 200 40
5 2 5 200 40
6 2 1 200 40
7 1 5 200 40


The output i want is.

product_id sum(numbers)in 2ndtable | sum(numbers)in 3rdtable
1 900 700
2 450 650

i need a query to do this. i m new to this. i spent lot of time in this but couldnt resolve. please help me.

 

This is my present query:

 

SELECT
    p.product_id,
    po.ponumbers,
    g.gonumbers
FROM
Product p
LEFT JOIN (SELECT Product_id, SUM(numbers) AS ponumbers
        FROM purchase_order
        GROUP BY product_id ) AS po ON p.product_id = po.product_id
LEFT JOIN (SELECT product_id, SUM(numbers) AS gonumbers
        FROM git_order
        GROUP BY product_id) AS g ON p.product_id = g.product_id

this gives the following output:

 

Array ( [0] => 1 [product_id] => 1 [1] => 19103 [ponumbers] => 19103 [2] => 18410 [gonumbers] => 18410 )

 

but i want not only for 1 product, i need the query to do the same for all the products that i have in my product table.

 

any help??

Edited May 21, 2013 by saravanataee

[Barand]

I just ran your query and the output is

+--------+-----------+-----------+
| prodid | ponumbers | gonumbers |
+--------+-----------+-----------+
|      1 |       900 |       700 |
|      2 |       450 |       650 |
+--------+-----------+-----------+
2 rows in set (0.00 sec)

So how are you creating your array?

[saravanataee]

I just executed the query into

 

$query=mysql_query("query");

 

print_r($query);

 

output is:

 

Array ( [0] => 1 [product_id] => 1 [1] => 19103 [ponumbers] => 19103 [2] => 18410 [gonumbers] => 18410 )

 

how did u execute it??

am i doing wrong in executing. plz let me know!!

[Barand]

$db = new mysqli(HOST, USERNAME, PASSWORD, DATABASE );

$sql = "SELECT
    p.product_id,
    po.ponumbers,
    g.gonumbers
FROM
Product p
LEFT JOIN (SELECT product_id, SUM(numbers) AS ponumbers
        FROM purchase_order
        GROUP BY product_id ) AS po ON p.product_id = po.product_id
LEFT JOIN (SELECT product_id, SUM(numbers) AS gonumbers
        FROM git_order
        GROUP BY product_id) AS g ON p.product_id = g.product_id";
        
echo query2HTMLtable($db, $sql);

/****************************************************
* Utility function to test queries
*****************************************************/
function query2HTMLtable($db, $sql)
{
    $output = "<table border='1'>\n";
    // Query the database
    $result = $db->query($sql);
    // check for errors
    if (!$result) return ("$db->error <pre>$sql</pre>");

    // get the first row and display headings
    $row = $result->fetch_assoc();
    $output .= "<tr><th>" . join('</th><th>', array_keys($row)) . "</th></tr>\n";
    
    // display the data
    do {
       $output .= "<tr><td>" . join('</td><td>', $row) . "</td></tr>\n"; 
    } while ($row = $result->fetch_assoc());
    $output .= "</table>\n";
    return $output;
}

I ran it from the mysql command line, but this should do it too