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
Solved

MySql link 2 foreign keys in different tables.

Posted on 2013-01-08
6
315 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 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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