Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SELECT Query to Merge Two tables

Posted on 2011-09-14
16
Medium Priority
?
282 Views
Last Modified: 2012-06-21

Please see attached. How should I merge two tables using select query or some other techniques?
Thanks.
Mergetbls.doc
0
Comment
Question by:andrishelp
[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
  • 6
  • 4
  • 3
  • +1
16 Comments
 
LVL 8

Expert Comment

by:MelMc
ID: 36537048
I am extremely confused as how these two tables are related.
In your results tables the first row, you have Applied Technologies and Staples Construction, they happen to have the same Contact.
In your results table in the second row, you have BKM and ARC, but they do not appear to have any relationship to each other. They do not have the same contact and all of the rows have the same CallId and CallDate.

What is the relationship between Table 1 and Table 2, how do you know that they should be merged for the results?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36537109
Ordinarily, the sql would go like this:

Select table1.*, table2.companyname, table2.contact from table1 left join table2 on table1.CallID  = table2.CallID


But, given your sample data, I'm not so sure.  
0
 

Author Comment

by:andrishelp
ID: 36537134


SELECT * ....
WHERE (T1Callid = T2callid) AND (T1calldate = T2calldate)

Rest of the columns does not matter whether they have same data or not.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 8

Expert Comment

by:MelMc
ID: 36537179
SELECT t1.*, t2.companyname, t2.contact
FROM table1 t1
JOIN table2 t2 ON t1.callid = t2.callid AND t1.calldate = t2.calldate
0
 

Author Comment

by:andrishelp
ID: 36537262
I wish the query is as simple as you mentioned here. I did try that but it was giving me a duplicate data for table 2. For example,

Each record in table 1 was linked to each record in table2. So instead of getting only 5 rows in the result set, I was getting 15 rows.

0
 
LVL 8

Expert Comment

by:MelMc
ID: 36537336
That's because based on your sample data the callid and calldate are the same for every row.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36537567
Looks like you need a FULL OUTER JOIN. The joining columns appear to be the combination of callid, calldate, and contact.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36537652
On second look, the contacts are not related. It was just a coincidence on the first row. However, FULL OUTER JOIN appears to be what you want for sure. It looks as though you want to match the lines up by their row number; therefore,

;WITH t1_ranked AS (
   SELECT callid, calldate, companyname, contact
        , ROW_NUMBER() OVER(PARTITION BY callid, calldate ORDER BY callid) rn
   FROM table1
), t2_ranked AS (
   SELECT callid, calldate, companyname, contact
        , ROW_NUMBER() OVER(PARTITION BY callid, calldate ORDER BY callid) rn
   FROM table2
)
SELECT COALESCE(t1.callid, t2.callid) AS Callid
     , COALESCE(t1.calldate, t2.calldate) AS Calldate
     , t1.companyname AS T1Companyname
     , t1.contact AS T1contact
     , t2.companyname AS T2Companyname
     , t2.contact AS T2contact
FROM t1_ranked t1
FULL OUTER JOIN t2 
   ON t1.callid = t2.callid AND t1.calldate = t2.calldate AND t1.rn = t2.rn
;

Open in new window


See my Article http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html for more details on ROW_NUMBER() windowing function and the OVER() analytical clause.

If the callid and calldate are not important to the ranking, i.e., you can join rows that have different callid and calldate values then remove the PARTITION BY bit. You can play with ORDER BY until you get desired match-ups.

Hope that helps!
0
 

Author Comment

by:andrishelp
ID: 36537678
FULL OUTER JOIN does not work either. I think it's not possible, what I am trying to do here. Thanks for all your help.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36538014
Hmm. Can you explain how you are determining it did not work? If it is not matching the rows you want, then the issue may be the ORDER BY. Definitely will yield a merge of the two tables ...
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36538019
I.e., did you actually run the query? If so, can you post what it resulting in and explain how it "should" have been.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36538378
To work, the tables need to be related.  That means, one of the columns in the first table needs to reflect which row(s) in the second table it is associated, based on a common value.  It's hard to tell from your test data because you seem to just put the same values in every row.  But with real data it may actually work.

Which column(s) uniquely identify a row in your table?

 
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36538476
dgmg is correct. That is the reason I added a ROW_NUMBER() to each table as that is what correlates the rows. Therefore, that is why I asked if you actually tried my query.
0
 

Author Closing Comment

by:andrishelp
ID: 36539179
Good Work! It does working the way I want that to be as per the document. Thanks!
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36539793
>dgmg is correct. That is the reason I added a ROW_NUMBER()

Not good enough.  ROW_NUMBER() is assigned based on callid which is the same in every row.  In other words, ROW_NUMBER() is arbitrary.  You may get the desired outcome coincidentally, but the query is still fundamentally flawed.  Unless, of couser, you don't care which row in table1 gets paired with which row in table 2.  >:)
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36539810
That is what I gathered and which is why I put to callid to make it select at random. That is also why I put caveat that ORDER BY should be adjusted if the association has any more logic to it. ;)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

636 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