Author |
Topic |
jfm
Posting Yak Master
145 Posts |
Posted - 2013-04-29 : 10:35:32
|
Hi there, I need to extract some data from two different tables when the data in ID_col from both files doesn't match. Any tip? Thanks |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-29 : 11:00:26
|
Perhaps this?SELECT a.*,b.*FROM TableA a FULL JOIN TableB b ON a.ID_col = b.ID_colWHERE a.ID_Col IS NULL OR b.ID_Col IS NULL; |
|
|
jfm
Posting Yak Master
145 Posts |
Posted - 2013-04-29 : 11:23:26
|
Thank you James, The matter is that using that query and I have: msg 207, level 16, state 3I have been trying to see what happens but I don't knowSELECT * into C FROM a AS a left outer join b AS bon a.id= b.idwhere b.id is nullThank youquote: Originally posted by James K Perhaps this?SELECT a.*,b.*FROM TableA a FULL JOIN TableB b ON a.ID_col = b.ID_colWHERE a.ID_Col IS NULL OR b.ID_Col IS NULL;
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-29 : 11:33:42
|
What does the message text say? It probably is complaining about duplicate column names. Change to:SELECT a.* into C FROM a AS a left outer join b AS bon a.id= b.idwhere b.id is null |
|
|
jfm
Posting Yak Master
145 Posts |
Posted - 2013-04-29 : 12:25:33
|
James, Thanks for that. Is working. The matter is that is not copying all the values from a new column: SELECT S.*, B.date FROM a AS aFULL JOIN b AS bON a.Id = b.idI dont undertand why is not copying all the data matching, in some cases is giving to me NULL value for that column that is a copy of b_table |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-29 : 12:32:40
|
That is to be expected. When you join on the id column and in the where clause specify that b.id is null, what you are requesting of SQL Server is to return all rows for which there were no matching rows in table b. Since there is no matching row in table b, there cannot be value for the date column in table b for that row as well.I didn't quite understand the logic you are trying to implement, but it seems like you need to join on something else other than id's. When you say "if the id columns don't match", that means you are (in your thought process) matching a row in table a with a row in table b using some other criterion. What is that criterion? That is what you would need to use in the join clause. |
|
|
jfm
Posting Yak Master
145 Posts |
Posted - 2013-04-29 : 12:55:40
|
Sorry James, I had extracted some info that is not in matching in table a and table b, in this case the ID_col extracted are ID's not matching in both filesWith that extraction I have to use address_col to link a date_col coming from table b. So SQL I will copy the date_col in the extracted file, using an address_col as the link between them.The matter is that the output given in date_col is just working for some rows not for all of them...Thank you jamesquote: Originally posted by James K That is to be expected. When you join on the id column and in the where clause specify that b.id is null, what you are requesting of SQL Server is to return all rows for which there were no matching rows in table b. Since there is no matching row in table b, there cannot be value for the date column in table b for that row as well.I didn't quite understand the logic you are trying to implement, but it seems like you need to join on something else other than id's. When you say "if the id columns don't match", that means you are (in your thought process) matching a row in table a with a row in table b using some other criterion. What is that criterion? That is what you would need to use in the join clause.
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-29 : 17:19:35
|
It is hard for me to follow the logic and data flow from your description. If you post the table schema and some representative sample data indicating the problem areas, that would help. Take a look at this page if you need help in posting schema and sample data: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
jfm
Posting Yak Master
145 Posts |
Posted - 2013-04-30 : 11:41:06
|
James. I found the issue with the query. It was a matter of tables. So thanks very much for your time quote: Originally posted by James K It is hard for me to follow the logic and data flow from your description. If you post the table schema and some representative sample data indicating the problem areas, that would help. Take a look at this page if you need help in posting schema and sample data: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
|
|
|
|