Mysql selecting with sum() and group by from multiple tables -

table 1

--------------------------------------- | id | item | mu | quantity | file_id | --------------------------------------- | 1  |item1 | oz | 3.5      | 003     | | 2  |item2 | kg | 1.1      | 001     | | 3  |item1 | oz | 0.2      | 001     | | 3  |item1 | kg | 3        | 001     | 

table 2

 ----------------------------  | id | date      | file_id |  ----------------------------  | 1  |timestamp1 | 001 |  | 2  |timestamp2 | 002     |  | 3  |timestamp3 | 003     | 

what i'm trying select sum of quantity each group of items have same mu. manage query.

$query = "select item,sum(quantity) table1 group item,mu";  great result, wanted ---------------------------------- | id | item | mu | sum(quantity) | ---------------------------------- | 1  |item1 | oz | 3.7           | | 2  |item2 | kg | 1.1           | | 3  |item1 | kg | 3             | 

but now, how only rows file_id date between 2 timestamps ?

you can manage achieve using left join , joining 2 tables in single query, replace initialdate , finaldate current timestamp

$query = "select a.id, a.item, a.mu, sum(a.quantity) total_quantity table1 left join table2 b on a.file_id = b.file_id b.date between initialdate , finaldate group a.item,a.mu";