Solved

MySql link 2 foreign keys in different tables.

Posted on 2013-01-08
6
306 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
Comment Utility
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
Comment Utility
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]
Comment Utility
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:rmartes
Comment Utility
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]
Comment Utility
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
Comment Utility
Excellent. Thanks.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 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

16 Experts available now in Live!

Get 1:1 Help Now