Solved

MySql link 2 foreign keys in different tables.

Posted on 2013-01-08
6
318 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 143

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 143

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 143

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Mysql sync between 3-4 mysql db 4 47
MS SQL Server - Looking to filter rows based on column value 3 56
MS Access - need to reduce row size 25 65
mysql query for sum() 3 47
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 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