Jump to content

Getting wron result merging tables


I-AM-OBODO
Go to solution Solved by I-AM-OBODO,

Recommended Posts

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 by Mr-Chidi
Link to comment
Share on other sites

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 by mac_gyver
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

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

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.