Need help in SQL join query - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help in SQL join query

Author

Topic

sachjn
Starting Member

2 Posts

Posted - 2007-07-26 : 13:16:14

Hi,
I have a two tables as below:

create table temp2
(
id int primary key,
val varchar(4)
);

insert into temp2 (id, val) values (1, NULL);
insert into temp2 (id, val) values (2, NULL);
insert into temp2 (id, val) values (3, '3');
insert into temp2 (id, val) values (4, '4');

create table temp3
(
id int primary key,
val varchar(4),
val2 varchar(4)

);

insert into temp3 (id, val) values (1, '1','1');
insert into temp3 (id, val) values (2, '', '11');
insert into temp3 (id, val) values (3, NULL,'11');
insert into temp3 (id, val) values (4, '4','11');

select * from temp3;

Now I want to fetch all rows from temp3 where temp3.val is not matching with temp2.val
Also with condition that NULL and '' is considered same while comparision.

Basically requirement is find all inconsistent rows of temp3 for the column "val". If my data being
perfect I should get zero rows after comparision.

So for above data set I should get results from temp3 as:

id val val2
--- --- ----
1 1 1
3 NULL 11

2 Rows fetched...

What would be the query to write that? I am pretty new to SQL queries.

I tried something like this but not sure of ISNULL check.

select
DISTINCT temp3.*
from temp3
LEFT OUTER JOIN temp2
ON ISNULL(temp3.val,'') = ISNULL(temp2.val,'')
WHERE temp2.val IS NULL OR temp2.val = ''

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-26 : 13:22:14

[code]
select T3.*
from @temp3 T3
Join @temp2 T2 ON T2.id = T3.id And (Case When T2.val IS NULL THen '' ELse T2.val end) <> (Case When T3.val IS NULL THen '' ELse T3.val end)
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sachjn
Starting Member

2 Posts

Posted - 2007-07-26 : 13:40:49

Thanks for reply. But I didn't get the syntax. Can't i use isnull?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-26 : 14:00:40

YEah you can youse Isnull(T2.val , '') <> Isnull(T3.val , '') or even Coalesce Coalesce(T2.val , '') <> coalesce(T3.val , ''). They all do the same thing. For smaller tables it probably does not make a difference but you will see a hit in pefformance for larger datasets when you use functions against columns.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources