MySql link 2 foreign keys in different tables.
Posted on 2013-01-08
I have 2 third party csv files being sent to me daily. I import each of these csv files into two tables in my MySql database (one table for each file) daily. Basically, the data in each file appends to the tables in the database. This causes a lot duplicates.
I want to use the extension column as the link between the two tables (table1.extension = table2.extension). The name column can change, but the extension stays the same. The problem lies when I want to sum up values:
select table1.extension, name, (sum(table1.calls) + sum(table2.voicemails)) total
from table1, table2
where table1.extension = table2.extension
and date between 12-01-12 and 12-02-12
group by table1.extension, table2.extension
The above sql groups them by extension and does not give me a total for each name listed in the tables. I want to list all the different names along with their totals even if they share the same extension. e.g.
Any suggestions? Hopefully I explained it well.