# What could be wrong

Go to solution Solved by Barand,

## Recommended Posts

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

##### Share on other sites

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

##### Share on other sites

• Solution

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

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.