Best way to perform this task

So I have 2 tables:

transaction:
t_id
t_amount
t_cat
t_user

categories
c_id

So I call the list of 'transactions' with SELECT * FROM transactions WHERE t_user = 'userid'.

This could load say 100 transactions.
There are 500 records in 'categories'.

The list of transactions could be across say 25 categories (therefore being multiple transactions for each category).

For each category applicable I want to calculate the total amount in value from t_amount.

What is the best way to do this?

The only thing I can think of is to load all the 'categories' then perform a loop for each category to get all transactions with that category ID and adding the value.

But this seems like it could be a very long process going through all the categories for just the few the user is using.........

You always want to pull the exact data you need from the DB. Don't use PHP to loop through the transactions. How is a transaction linked to a category? You are going to want to join the tables.

why dont you use the concepts of joins here so that the query will be easy... and can get your results...
:)

I've asked the mods to move this as it's a Database question.

And the others are right... what you need to do is join the two tables together... depending on the results you want, it can be either an inner join - which give you only the rows that exist in both tables - or a left join - which would give you all the rows in the left table, plus any that match in the right table.


so, something like this:

Code:

---

select c.c_id, sum(t.t_amount) as Total
from categories c
inner join transaction t on c_id = t_cat
group by c.c_id

---

How ever, I STRONGLY suggest you pick another table name for your Transaction table, as Transaction really is a reserved word and can cause problems for you later....


-tg

Quote:

---

Originally Posted by techgnome

I've asked the mods to move this as it's a Database question.

---

And we have listened......moved to the database development section.