CODEWITHSUNDEEP
phpmysql
Asim Siddiqui
Asim Siddiqui

Reputation: 309

MYSQL Nested Queries

The purpose of asking this question is just to learn how to make nested queries in MYSQL.

1) What is wrong with the following query?

"SELECT tblwriter.writer_alias, tblwriter.writer_first_name, COUNT(tblordersub.suborder_alias) AS totalOrders FROM tblwriter, tblordersub WHERE tblwriter.writer_isactive = 1 AND tblordersub.writer_alias = tblwriter.writer_alias"

2) Can there be a better solution for the following nest of MYSQL queries?

$currentorders = $db->rawQuery("SELECT order_title,order_alias FROM tblorder WHERE company_id=? AND YEAR(order_date) = YEAR(CURDATE()) AND MONTH(order_date) = MONTH(CURDATE())",$params);
$orderssummary = array();
if(!empty($currentorders)){
    foreach($currentorders as $corder){
        $param = array($corder["order_alias"]);
        $oprice = $db->rawQuery("SELECT payment_amount FROM tblpayment WHERE payment_status = 1 AND writer_alias IS NULL AND order_alias=?",$param);
        $itssuborders = $db->rawQuery("SELECT suborder_alias FROM tblordersub WHERE order_alias=?",$param);
        $thesuborders = array_implode("",",",$itssuborders);
        $cost = $db->rawQuery("SELECT SUM(payment_amount) AS total_subtotal FROM tblpayment WHERE writer_alias IS NOT NULL AND suborder_alias IN (".$thesuborders.")");
        $orderssummary[] = array("title"=>$corder["order_title"],"price"=>$oprice[0]["payment_amount"],"cost"=>$cost[0]["total_subtotal"]);
    }
}

3) Is there any way to combine the following three queries into 1?

"SELECT SUM(payment_amount) AS totalAmount FROM tblpayment WHERE company_id=? AND payment_status = 1 AND order_alias IS NOT NULL AND YEAR(payment_add_datetime) = YEAR(CURDATE()) AND MONTH(payment_add_datetime) = MONTH(CURDATE() - INTERVAL 2 MONTH)"
"SELECT SUM(payment_amount) AS totalAmount FROM tblpayment WHERE company_id=? AND payment_status = 0 AND writer_alias IS NOT NULL AND YEAR(payment_add_datetime) = YEAR(CURDATE()) AND MONTH(payment_add_datetime) = MONTH(CURDATE() - INTERVAL 2 MONTH)"
"SELECT SUM(payment_amount) AS totalAmount FROM tblpayment WHERE company_id=? AND payment_status = 1 AND writer_alias IS NOT NULL AND YEAR(payment_add_datetime) = YEAR(CURDATE()) AND MONTH(payment_add_datetime) = MONTH(CURDATE() - INTERVAL 2 MONTH)"

4) Same as 2nd question, is there any way to combine the following queries into 1 query?

$biggest_customers = $db->rawQuery("SELECT payment_user_id, SUM(payment_amount) AS totalEARNED FROM tblpayment WHERE writer_alias IS NULL AND order_alias IS NOT NULL GROUP BY payment_user_id ORDER BY totalEARNED DESC LIMIT 10");
for($i=0;$i<count($biggest_customers);$i++){
    $params = array($biggest_customers[$i]["payment_user_id"]);
    $customerinformation = $db->rawQuery('SELECT customer_alias FROM tblcustomer WHERE user_id=?',$params);
    $biggest_customers[$i]["customer_alias"] = $customerinformation[0]["customer_alias"];
    unset($biggest_customers[$i]["payment_user_id"]);
}

Hope to learn somethings!!

Upvotes: 0

Views: 140

Answers (2)

Jan Wy
Jan Wy

Reputation: 1569

you can not use the COUNT() statement (aggregation query) without a GROUP BY when selecting multiple columns. You also should rather use a join statement to select values from different tables. Something like this maybe:

SELECT tblwriter.writer_alias, tblwriter.writer_first_name, COUNT(tblordersub.suborder_alias) AS totalOrders 
FROM tblwriter 
INNER JOIN tblordersub ON (tblordersub.writer_alias = tblwriter.writer_alias) 
WHERE tblwriter.writer_isactive = 1
GROUP BY tblordersub.suborder_alias

By just using FROM tblwriter, tblordersub in your query you are creating a cross join, which is the cartesian product of the data in your tables. You probably don't want that.

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

  1. See aggregate queries
  2. See JOINs
  3. See CASE statements/PIVOT TABLE queries
  4. See JOIN again

Upvotes: 2

Related Questions