Solved

MySql link 2 foreign keys in different tables.

Posted on 2013-01-08
6
310 Views
Last Modified: 2013-01-10
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
0
Comment
Question by:rmartes
  • 3
  • 3
6 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38757889
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
 

Author Comment

by:rmartes
ID: 38763695
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38763895
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:rmartes
ID: 38764429
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38764713
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
 

Author Closing Comment

by:rmartes
ID: 38765069
Excellent. Thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now