SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Not matching
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jfm
Posting Yak Master

145 Posts

Posted - 04/29/2013 :  10:35:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 04/29/2013 :  11:00:26  Show Profile  Reply with Quote
Perhaps this?
SELECT  a.*,b.*
FROM    TableA a
        FULL JOIN TableB b ON a.ID_col = b.ID_col
WHERE   a.ID_Col IS NULL
        OR b.ID_Col IS NULL;
Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 04/29/2013 :  11:23:26  Show Profile  Reply with Quote
Thank you James,

The matter is that using that query and I have:

msg 207, level 16, state 3

I have been trying to see what happens but I don't know

SELECT * into C FROM a AS a
left outer join b AS b
on a.id= b.id
where b.id is null

Thank you


quote:
Originally posted by James K

Perhaps this?
SELECT  a.*,b.*
FROM    TableA a
        FULL JOIN TableB b ON a.ID_col = b.ID_col
WHERE   a.ID_Col IS NULL
        OR b.ID_Col IS NULL;


Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 04/29/2013 :  11:33:42  Show Profile  Reply with Quote
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 b
on a.id= b.id
where b.id is null
Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 04/29/2013 :  12:25:33  Show Profile  Reply with Quote
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 a
FULL JOIN b AS b
ON a.Id = b.id

I 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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 04/29/2013 :  12:32:40  Show Profile  Reply with Quote
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.
Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 04/29/2013 :  12:55:40  Show Profile  Reply with Quote
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 files

With 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 james


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

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 04/29/2013 :  17:19:35  Show Profile  Reply with Quote
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
Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 04/30/2013 :  11:41:06  Show Profile  Reply with Quote
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


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000