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?
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.
If you're not pulling in data from any other tables, and all you need from the transaction table is the date, then use MAX with a GROUP BY on the user.
SELECT c.firstname, c.surname, c.regDate, MAX(t.lastTrans) AS lastTrans
FROM confirmed c
LEFT JOIN transaction t ON c.user_id = t.user_id
WHERE c.status = 'confirmed'
GROUP BY c.user_id
ORDER BY MAX(t.lastTrans)