CODEWITHSUNDEEP
sqljoinhive
AKIWEB
AKIWEB

Reputation: 19612

Combine two SQL Tables

Join both the tables and get first table data and second table data which is missing in Table1 by comparing on Col1 and Col11 I guess. As all the ID's (Col1 and Col11) should come together

Table1

Col1      Col2            Col3                  Col4      Col5            Col6 
1345653   330760137950    2012-07-09 21:40:29   1345653   331760137950    1341895229
1345653   110909316904    2012-07-09 21:29:06   1345653   111909316904    1341894546
1345653   221065796761    2012-07-09 19:31:48   1345653   221065796761    1341887518

Table2

Col11        Col22         Col33                  
1345653      330760137950   2012-07-09 21:40:29     
1345653      110909316904   2012-07-09 21:29:06     
1345653      221065796761   2012-07-09 19:31:48     
1345653    **150851771618**   2012-07-09 18:57:33

If you look above two tables data, last line in Table2 means Col22-150851771618 is missing in Table1. So I need to display Table1 full data and last line from Table2 as Col22 is missing in Table1 by joining on Col1 and Col11, like this below output.

1345653   330760137950    2012-07-09 21:40:29   1345653   331760137950    1341895229
1345653   110909316904    2012-07-09 21:29:06   1345653   111909316904    1341894546
1345653   221065796761    2012-07-09 19:31:48   1345653   221065796761    1341887518
1345653 **150851771618**  2012-07-09 18:57:33   NULL       NULL           NULL

How can I do this using JOIN by joining on Col1 and Col11? I am having lot of confusion on this. Can anyone help me?

Update:- Some more scenarios

Table1

Col1            Col2            Col3                   Col4       Col5                Col6
1345653     330760137950    2012-07-09 21:40:29     1345653     331760137950        1341895229
1345653     110909316904    2012-07-09 21:29:06     1345653     111909316904        1341894546
1345653     221065796761    2012-07-09 19:31:48     1345653     221065796761        1341887518

704318001   320941581940    2012-07-09 14:44:48     704318001   321941581940        1341870288

Table2

Col11        Col22         Col33                   Col44   Col55   Col66
1345653      330760137950   2012-07-09 21:40:29     NULL    NULL    NULL
1345653      110909316904   2012-07-09 21:29:06     NULL    NULL    NULL
1345653      221065796761   2012-07-09 19:31:48     NULL    NULL    NULL
1345653    **150851771618**   2012-07-09 18:57:33   NULL    NULL    NULL

704318001  **290738585064**    2012-07-09 14:36:49     NULL    NULL    NULL

So Output should be like this- Meaning for this 1345653 ID, I need all record at same place and same with 704318001. So if you look the output, all the 1345653 are together and all 704318001 are together.

1345653   330760137950    2012-07-09 21:40:29   1345653   331760137950    1341895229
1345653   110909316904    2012-07-09 21:29:06   1345653   111909316904    1341894546
1345653   221065796761    2012-07-09 19:31:48   1345653   221065796761    1341887518
1345653 **150851771618**  2012-07-09 18:57:33   NULL       NULL           NULL


704318001   320941581940    2012-07-09 14:44:48     704318001   321941581940        1341870288 
704318001  **290738585064**    2012-07-09 14:36:49     NULL    NULL    NULL

Basically, get the first table full and second table whose data is not there in Table1

Upvotes: 2

Views: 955

Answers (4)

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

As I see it you need to add rows from table2 where you cannot find a match on first three columns in table1. You can combine union all and exists to that effect. If the database supports except you might use it instead of exists.

select Col1, Col2, Col3, Col4, Col5, Col6
  from table1
union all
select Col11, Col22, Col33, null, null, null
  from table2
 where not exists (select null
                     from table1
                    where table1.col1 = table2.col11
                      and table1.col2 = table2.col22
                      and table1.col3 = table2.col33)

UPDATE after clarification: if you can use union all and left join this SQL FIDDLE should help:

select buyer_id, item_id, created_time, user_id, product_id, timestamps
  from TestingTable1
union all
select t2.buyer_id, t2.item_id, t2.created_time, null, null, null
  from TestingTable2 t2
  left join TestingTable1 t1
    on t1.buyer_id = t2.buyer_id
   and t1.item_id = t2.item_id
   -- remove this line if you identify duplicated record
   -- by buyer and item only
   and t1.created_time = t2.created_time
 where t1.buyer_id is null

Upvotes: 1

Marzena
Marzena

Reputation: 363

I am not really sure, what exactly is the expected output - for example, do you need just a partial outer join or full outer join? assuming you need full outer join and this might do the trick (T-SQL syntax):

select
    coalesce(t1.col1, t1.col11),
    coalesce(t1.col2, t1.col22),
    coalesce(t1.col3, t1.col33),
    coalesce(t1.col4, t1.col44),
    coalesce(t1.col5, t1.col55),
    coalesce(t1.col6, t1.col66),
from
    Table1 t1
    full outer join Table2 t2 on t2.Col22 = t1.Col2

hope this is what you need. It should join all rows from both Tables on column Col2-Col22 and display for each column pair value from Table1 and if there is a null value it should display value from Table2

edit: you can not join the tables on Col1-Col11. That would in fact produce cartesian product on this example. But may be - depends on real meaning of the data - could be useful join on both Col1=Col11 and Col2=Col22

Upvotes: 0

pyrometer
pyrometer

Reputation: 881

Why not simply -

SELECT * FROM Table2;

Else please make the question clearer.

Upvotes: 0

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

A LEFT JOIN should work..

But based on your data, I think you should be joining on Col22 to Col2, since Col1 and Col11 don't have unique values, so I don't know how they are associated.

SELECT t2.Col11, t2.Col22, t2.Col33, t1.Col4, t1.Col5, t1.Col6
FROM 
    Table2 t2 LEFT JOIN
    Table1 t1 ON t1.Col2 = t2.Col22

Upvotes: 4

Related Questions