Getting wron result merging tables

[I-AM-OBODO]

Hello guys.

I’m having trouble understanding JOIN and GROUP.

I have two tables that I want to merge into one but when I try it, the result is not what is expected and I don’t know where I’m wrong. I want to merge Table A and B to get Table C as shown below:

 

Table A

Client Name        Username        Amount Deposited

John  Doe        joh@doe.com        2500

Julian Cram        jul@cram.com        2000

Peter Stalone        pet@sta.com        1200

Creig Davies        creg@davies.com        3000


Table B

Client Name        Username        Invoice Amount

John  Doe        joh@doe.com        1000

Julian Cram        jul@cram.com        500

Peter Stalone        pet@sta.com        4500

Creig Davies        creg@davies.com        1500


Table C

Client Name        Username        Invoice Amount        Amount Deposited

John  Doe        joh@doe.com        1000            2500

Julian Cram        jul@cram.com        500            2000

Peter Stalone        pet@sta.com        4500            1200

Creig Davies        creg@davies.com        1500            3000

 
echo "<table width='100%' class='table table-striped tbl'>";
echo "<tr>
<th bgcolor='#444444' align='center'><font color='#fff'>Client's Name</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Username</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Amount Deposited</font></th>
</tr>";
 
$stmt = $pdo->query("
SELECT d.firstname, d.surname, d.username, SUM(d.amt_deposited) AS sum_deposited
FROM ca_my_payments d GROUP BY d.username
");
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
echo "<tr><td>";
$client = ucwords($row['firstname'] . " " .$row['surname']);
echo $client;
echo "</td><td>";
echo $row['username'];
echo "</td><td>";
echo $row['sum_deposited'];
echo "</tr></td>";
}
echo "</table>";
 
echo "<br><br>";
 
echo "<table width='100%' class='table table-striped tbl'>";
echo "<tr>
<th bgcolor='#444444' align='center'><font color='#fff'>Client's Name</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Username</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Invoice</font></th>
</tr>";
               
$stmt = $pdo->query("
SELECT p.payee, p.username, SUM(p.total_payment) AS total_invoice
FROM ca_processed p GROUP BY p.username
");
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "<tr><td>";
echo $row['payee'];
echo "</td><td>";
echo $row['username'];
echo "</td><td>";
echo number_format($row['total_invoice'],2);
echo "</tr></td>";
}
echo "</table>";
echo "<br><br>";
 
echo "<table width='100%' class='table table-striped tbl'>";
echo "<tr>
<th bgcolor='#444444' align='center'><font color='#fff'>Client's Name</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Username</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Invoice</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Amount Deposited</font></th>
<th bgcolor='#444444' align='center'><font color='#fff'>Current Balance</font></th>
</tr>";
                               
$stmt = $pdo->query("
SELECT a.payee, a.username, b.username, SUM(a.total_payment) AS total_invoice, SUM(b.amt_deposited) AS sum_deposited
FROM ca_processed a
LEFT JOIN ca_my_payments b ON a.username = b.username
GROUP BY a.username
");
 
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "<tr><td>";
echo $row['payee'];
echo "</td><td>";
echo $row['username'];
echo "</td><td>";
echo number_format($row['total_invoice'],2);
echo "</td><td>";
echo number_format($row['sum_deposited'],2);
echo "</tr></td>";
}
echo "</table>";

Edited January 18, 2016 by Mr-Chidi

[Barand]

Does that last query give the correct results that should be inserted into tableC?

[I-AM-OBODO]

Does that last query give the correct results that should be inserted into tableC?

 

No it doesnt. The result is different from what is expected.

[mac_gyver]

a JOIN is used when you have related data between the tables.

 

an example would be a user table and your  ca_processed table. a query to retrieve user information and his related row(s) from the  ca_processed table would use a (LEFT) JOIN query. the user_id, defined in the user table, would be used in the  ca_processed table to relate the user's rows in the  ca_processed table to the user they belong to.

 

your two tables don't have any defined relationship between them. if you have multiple rows in ca_processed for any username and multiple rows in ca_my_payments for the same username, you will get the result of every row for that username in the first table joined to every row for that username in the second table.

 

if you had an invoice table (which i guess is what your ca_processed table is), that assigned an invoice_id to the invoiced amount and you stored row(s) in a payment table, related to the corresponding invoice using the invoice_id, you would use a (LEFT) JOIN query to get the amount of the invoice and any amounts paid toward that invoice because you now have a defined relationship between the data in the two tables.

 

the reason i mentioned a user table in the example of a join query, is because you shouldn't have usernames/emails duplicated in these two tables. you should have the user information stored in only one place, then use the user_id in any tables holding information that's related to the user.

Edited January 18, 2016 by mac_gyver

[I-AM-OBODO]

a JOIN is used when you have related data between the tables.

 

an example would be a user table and your  ca_processed table. a query to retrieve user information and his related row(s) from the  ca_processed table would use a (LEFT) JOIN query. the user_id, defined in the user table, would be used in the  ca_processed table to relate the user's rows in the  ca_processed table to the user they belong to.

 

your two tables don't have any defined relationship between them. if you have multiple rows in ca_processed for any username and multiple rows in ca_my_payments for the same username, you will get the result of every row for that username in the first table joined to every row for that username in the second table.

 

if you had an invoice table (which i guess is what your ca_processed table is), that assigned an invoice_id to the invoiced amount and you stored row(s) in a payment table, related to the corresponding invoice using the invoice_id, you would use a (LEFT) JOIN query to get the amount of the invoice and any amounts paid toward that invoice because you now have a defined relationship between the data in the two tables.

 

the reason i mentioned a user table in the example of a join query, is because you shouldn't have usernames/emails duplicated in these two tables. you should have the user information stored in only one place, then use the user_id in any tables holding information that's related to the user.

thanks for the tips. my problem is just table c. i guess what they all have in common is the username and so far all the joins work. the database is been designed already like this but with time will make changes. the case on ground now is to have them together.

 

thanks

[mac_gyver]

your current database design will only work, using a JOIN query, if each username only has one row in the ca_processed (invoice) table, which would be useless in a real application. in this case, the username does become the value that relates any row(s) in the ca_my_payments table to the one correct row in the ca_processed table. you would however not use SUM(p.total_payment) to come up with the total_invoice amount as that would repeatedly add the amount for each JOINed row between the two tables.

 

if you have more than one row for any username in the ca_processed table, you can use a UNION query to sum up all the invoice amounts and the payment amounts for each username, but i doubt that's your goal.

[I-AM-OBODO]

your current database design will only work, using a JOIN query, if each username only has one row in the ca_processed (invoice) table, which would be useless in a real application. in this case, the username does become the value that relates any row(s) in the ca_my_payments table to the one correct row in the ca_processed table. you would however not use SUM(p.total_payment) to come up with the total_invoice amount as that would repeatedly add the amount for each JOINed row between the two tables.

 

if you have more than one row for any username in the ca_processed table, you can use a UNION query to sum up all the invoice amounts and the payment amounts for each username, but i doubt that's your goal.

Thanks. if you look at table A and table B, i used a JOIN to sum up the values and GROUP them based on the user name and it worked fine. i just want to have the SUMs in one table (table C) instead of two tables (A & B). maybe UNION could do it, can u pls come up with an example on UNION?

Thanks

[I-AM-OBODO]

Thanks all.

UNION did the magic. Credit to r937 of devshed. i dunno if posting the link to the solved issue is prohibited cos i don't want go against the laws of the forum.