Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Pirre0001
Starting Member
19 Posts |
 Posted - 2014-03-10 : 16:59:52
|
| 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-03-10 : 17:15:48
|
Something like this - you can concatenate other conditions as requiredselect * from Table1 t1 inner join Table2 t2 on t1.id = t2.idwhere ((t1.field1 = 1 and t2.field1 is null) or (t1.field1 = 0 and t2.field1 is not null)) --- and so on. |
 |
|
|
Pirre0001
Starting Member
19 Posts |
Posted - 2014-03-10 : 17:31:17
|
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 requiredselect * from Table1 t1 inner join Table2 t2 on t1.id = t2.idwhere ((t1.field1 = 1 and t2.field1 is null) or (t1.field1 = 0 and t2.field1 is not null)) --- and so on.
|
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-03-10 : 19:17:05
|
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)) |
|
|
|
|
|
|
|
|
Match fields?