SELECT Query to Merge Two tables

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?

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.  

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

Rest of the columns does not matter whether they have same data or not.

SELECT t1.*, t2.companyname, t2.contact
FROM table1 t1
JOIN table2 t2 ON t1.callid = t2.callid AND t1.calldate = t2.calldate

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.

That's because based on your sample data the callid and calldate are the same for every row.

Looks like you need a FULL OUTER JOIN. The joining columns appear to be the combination of callid, calldate, and contact.

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.

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 ...

I.e., did you actually run the query? If so, can you post what it resulting in and explain how it "should" have been.

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?

 

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.

Good Work! It does working the way I want that to be as per the document. Thanks!

>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.  >:)

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. ;)