case when - 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)

case when

Author

Topic

amits
Starting Member

6 Posts

Posted - 2010-10-06 : 01:13:28

I have a main table to which I need to append the data from two other tables - tableA and tableB. I want to add a new variable to main table that says "match" if it has a match from tableA and "others" if it has a match from tableB. Here is the data:

Maintable
AcctNo Name
123 Jason Smith
111 Tom Altar
222 Jessica Mo
11144 Tracy Smit
5421 Jack Brown
364 Jack Daniels

TableA
AcctNo Surname Firstname
123 Jason Smith
11144 Tracy Smit

TableB
AcctNo Surname Firstname
111 Tom Altar
5421 Jack Brown

RESULTS SHOULD LOOK SOMETHING LIKE

AcctNo Name Surname Firstname Match
123 Jason Smith Jason Smith Exact
111 Tom Altar Tom Altar Other
222 Jessica Mo
11144 Tracy Smit Tracy Smit Exact
5421 Jack Brown Jack Brown Other
364 Jack Daniels

amits
Starting Member

6 Posts

Posted - 2010-10-06 : 01:21:01

sorry I forgot to mention that I can't use UNION to combine TableA and tableB as in reality there are few different fields in two tables.

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-10-06 : 02:04:01

declare @Maintable as table
(
acctno int,
name varchar(20)
)

declare @TableA as table
(
acctno int,
Surname varchar(20),
firstname varchar(20)
)

declare @Tableb as table
(
acctno int,
Surname varchar(20),
firstname varchar(20)
)
insert into @Maintable

select 123, 'Jason Smith'
union select 111, 'Tom Altar'
union select 222, 'Jessica Mo'
union select 11144, 'Tracy Smit'
union select 5421, 'Jack Brown'
union select 364, 'Jack Daniels'

insert into @TableA
select 123, 'Jason', 'Smith'
union select 11144, 'Tracy', 'Smit'
insert into @Tableb
select 111, 'Tom', 'Altar'
union select 5421, 'Jack', 'Brown'

select temp.acctno,temp.name,
COALESCE(asurname,bsurname,'') surname,
COALESCE(Afirstname,bfirstname,'') firstname,
case COALESCE(A,B,0) when A then 'Exact' when B then 'Other' Else '' end Match
from
(
select m.*,a.acctno A,a.firstname Afirstname,a.Surname asurname,b.acctno B,b.firstname bfirstname,b.Surname bsurname from @Maintable m
left outer join @TableA a on m.acctno = a.acctno
left outer join @Tableb b on m.acctno = b.acctno
) temp

Sachin.Nand

2937 Posts

Posted - 2010-10-06 : 02:38:47

I think there is no need for the derived table


select m.acctno,
       m.name,
       COALESCE(a.surname,b.surname,'') surname,
       COALESCE(A.firstname,b.firstname,'') firstname,
       case COALESCE(a.acctno,b.acctno,0) when  a.acctno then 'Exact' when b.acctno then 'Other' Else '' end Match
 from @Maintable m
left outer join @TableA a on m.acctno = a.acctno
left outer join @Tableb b on m.acctno = b.acctno

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources