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
 Match fields?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pirre0001
Starting Member

19 Posts

Posted - 03/10/2014 :  16:59:52  Show Profile  Reply with Quote
I have four fields in two different tables.
ID, field1, field2 and field3 in Table 1 has value 1 or 0. Type integer.
ID, field1, field2 and field3 in table2 are text fields.
If field1 in table1 has value 1, then there should be a text value in corresponding field in table2. That is, in field1 in table2.
However, if there is value 0 in field1 in table1 there should not be any value in the corresponding field in table2.
Now it is not always so, which do that I want to make a track of which fields miss match in these two tables.
How do I list them on the smartest ways? ID links the tables.

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 03/10/2014 :  17:15:48  Show Profile  Reply with Quote
Something like this - you can concatenate other conditions as required
select 
	* 
from 
	Table1 t1 inner join Table2 t2 on 
	t1.id = t2.id
where
	((t1.field1 = 1 and t2.field1 is null) or (t1.field1 = 0 and t2.field1 is not null))
	--- and so on.
Go to Top of Page

Pirre0001
Starting Member

19 Posts

Posted - 03/10/2014 :  17:31:17  Show Profile  Reply with Quote
If t2.field1 has had a value, it will be empty and not null. Null is if it never had any value. Can I bake it in any smart way?

quote:
Originally posted by James K

Something like this - you can concatenate other conditions as required
select 
	* 
from 
	Table1 t1 inner join Table2 t2 on 
	t1.id = t2.id
where
	((t1.field1 = 1 and t2.field1 is null) or (t1.field1 = 0 and t2.field1 is not null))
	--- and so on.


Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 03/10/2014 :  19:17:05  Show Profile  Reply with Quote
By empty, if you meant empty string, change the where clause to
((t1.field1 = 1 and NULLIF(t2.field1,'') is null) or (t1.field1 = 0 and NULLIF(t2.field1,'') is not null))
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