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
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
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
Why not simply -
SELECT * FROM Table2;
Else please make the question clearer.
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
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
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
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
Why not simply -
SELECT * FROM Table2;
Else please make the question clearer.
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
0 commentaires:
Enregistrer un commentaire