What could be wrong

[I-AM-OBODO]

Hello all.

please what could be the reason i am not getting the desired result when i do a JOIN.

This query gives the desired result
 

//calculate profit
$stmt = $pdo->query("
SELECT 
SUM(total_cost) AS total_cost,
SUM(total_sales) AS total_sales
FROM tbl_sales
WHERE customer_id = 489639
AND status = 0

");

$row = $stmt->fetch(PDO::FETCH_ASSOC);
$total_sales  = $row['total_sales'];
$total_cost   = $row['total_cost'];
$profit = $total_sales - $total_cost;

//calculate balance

$stmt = $pdo->query("
SELECT 
SUM(amt_paid) AS amt_paid
FROM tbl_sales_total
WHERE customer_id = 489639
AND status = 0

");

$row = $stmt->fetch(PDO::FETCH_ASSOC);
$amt_paid = $row['amt_paid'];
$balance = $total_sales - $amt_paid;

echo "<strong>Total Profit Realised From Customer: ₦".$profit."</strong>";
echo "<br><strong>Total Unpaid Balance: ₦".$unpaid_bal."</strong>";

But when i do a join it gives a wrong result.

$stmt = $pdo->query("
SELECT *,
SUM(t2.total_price) AS total_sales,
SUM(t3.amt_paid) AS amt_paid
FROM customers t1
LEFT JOIN tbl_sales t2 ON t1.customer_id = t2.customer_id
LEFT JOIN tbl_sales_total t3 ON t1.customer_id = t3.customer_id
WHERE t1.customer_id = 489639
AND t3.status = 0                     

");

WHILE($row = $stmt->fetch(PDO::FETCH_ASSOC)){

echo   '<tr>
<td>'.$row["customer_name"].'</td>
<td>'.$row["total_sales"].'</td>
<td>'.$row["amt_paid"].'</td>
</td>
</tr>';
}

Please what did i do wrong and how do i get the desired result.

 

Thanks

[requinix]

Post the row data in those three tables for that customer_id, and include what output you expected to see.

[Barand]

Whe you join a table with M records to a table with N matching records you get M*N records returned. Therefore you are now totalling Nx more records than before.

You need to ensure you join 1 row from 1 tablw with 1 matching record in the other. You can do this with subqueries to get the individal tables' totals.

SELECT t1.customer_id
     , t2.total_sales
     , t3.amt_paid
FROM customers t1
     LEFT JOIN 
        (
            SELECT customer_id
                 , SUM(total_price) as total_sales
            FROM tbl_sales
            GROUP BY customer_id
        ) t2 USING (customer_id)
     LEFT JOIN
        (
            SELECT customer_id
                 , SUM(amt_paid) as amt_paid
            FROM tbl_sales_total
            WHERE status = 0
            GROUP BY customer_id
        ) t3 USING (customer_id)
WHERE t1.customer_id = 489639

Also, you have a LEFT JOIN to tbl_sales_total t3. You therefore cannot use WHERE t3.status = 0. That condition needs to go into the join condition otherwise the query behaves as though it were an ordinary (INNER) JOIN

[I-AM-OBODO]

It worked fine as expected. Thank you so much. But one other thing is with these joins, how can i use it to get same result but without specifying the customer? When I removed the where clause, it throws an error.

I also know I can get the difference between total_sales and amt_paid by first getting the values of each and then minus it. But I am curious to know if it can be achieved using the mysql query? If possible, how can it be done?

Thanks so much as always!

Besides, how can one know when to use the multi level joins you used? Joins so complicated. 

 

Thanks

[I-AM-OBODO]

9 minutes ago, I-AM-OBODO said:

It worked fine as expected. Thank you so much. But one other thing is with these joins, how can i use it to get same result but without specifying the customer? When I removed the where clause, it throws an error.

I also know I can get the difference between total_sales and amt_paid by first getting the values of each and then minus it. But I am curious to know if it can be achieved using the mysql query? If possible, how can it be done?

Thanks so much as always!

Besides, how can one know when to use the multi level joins you used? Joins so complicated. 

 

Thanks

I have seen why it was throwing error. Its cos of values that are 0. And number_format use with value 0 is deprecated. I have resolved the error issue but the problem now is that the grouping does not working in the final execution of the query. It brings out the new multiple times. How can I fix this. 

Thanks

[I-AM-OBODO]

The duplicate names I have addressed. All that's left is to know if the minus could be done in the query. Other than that, it's working just fine.

Thanks a million times

[I-AM-OBODO]

I've been able to do the minus from within the query now. After everything, the working query is

SELECT t1.customer_id, t1.customer_name,
COALESCE(t2.total_sales, 0) as total_sales,
COALESCE(t3.amt_paid, 0) as amt_paid,
COALESCE(t2.total_sales, 0) - COALESCE(t3.amt_paid, 0) as to_balance
FROM customers t1
LEFT JOIN 
(
SELECT customer_id,
SUM(total_price) as total_sales
FROM tbl_sales
GROUP BY customer_id
) t2 USING (customer_id)
LEFT JOIN
(
SELECT customer_id,
SUM(amt_paid) as amt_paid
FROM tbl_sales_total
WHERE status = 0
GROUP BY customer_id
) t3 USING (customer_id)
HAVING to_balance <> 0
ORDER BY t1.customer_name ASC;

Thanks @Barand