MySql link 2 foreign keys in different tables.

Hi Experts,

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.

table1

extension|name|calls|date_imported
1234|sam|2|12-01-12
4321|paul|4|12-01-12
1234|john|6|12-02-12
4321|paul|8|12-02-12

table2

extension|name|voicemails|date_imported
1234|sam|4|12-01-12
4321|paul|8|12-01-12
1234|john|12|12-02-12
4321|paul|16|12-02-12

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.

extension|name|total
1234|sam|6
1234|john|18
4321|paul|36

Any suggestions? Hopefully I explained it well.

Thanks
rmartesAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you need to UNION and not to JOIN the tables...
select extension, name, sum(total)
from (
select t1.extension, t1.name, sum(t1.calls) total
from table1 t1
where t2.date between '2012-01-12' and '2012-02-12'
group by t1.extension, t1.name
UNION ALL
select t2.extension, t2.name, sum(t2.voicemails) total
from table2 t2
where t2.date between '2012-01-12' and '2012-02-12'
group by t2.extension, t2.name
) sq
group by extension, name

Open in new window

0
 
rmartesAuthor Commented:
Worked nicely! One more question before I accept solution. I got an extension that is not found in either table, but I still want to display it with all zeros. How would I get it to display records returning all zeros in this situation.

I know how to do it when their is a primary key and the key exists, but this situation is different as there is no primary key.

Thanks again.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you have a table with all the extensions, you can UNION ALL that table

select extension, name, sum(total)
from (
select t1.extension, t1.name, sum(t1.calls) total
from table1 t1
where t2.date between '2012-01-12' and '2012-02-12'
group by t1.extension, t1.name
UNION ALL
select t2.extension, t2.name, sum(t2.voicemails) total
from table2 t2
where t2.date between '2012-01-12' and '2012-02-12'
group by t2.extension, t2.name
UNION ALL
select t3.extension, 'x', 0
  from your_exensions_table t3
) sq
group by extension, name
                                           

Open in new window


if that table has also the usernames, replace the 'x' by the relevant field
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
rmartesAuthor Commented:
That's the problem. I don't have a table with all the extensions. It's just those two tables in the example. My guess is that it is not possible to display zeros without one? Right?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
correct. if you have some extension that is not in at least either of the 2 tables, the query won't produce any records for that extension.
0
 
rmartesAuthor Commented:
Excellent. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.