SQLTeam.com | Weblogs | Forums

Sql Help - Case Statement by comparing


#1

Hi All
I'm trying to get customer name based on two tables . Some of our customers will have more than one subsidiary and when they request information from us we save master information in 1 table and subsidiary information in another table .

Note1: There may be times when they just select 1 subsidiary for a request.
Note2: There may be times that 2 subsidiaries might have the same name
Note3 : Sometimes Customer name and subsidiary name are one and the same

Table 1 contains the master name of the customer
Table 2 contains subsidiary names for a request

Table 1
SNO
CustomerName

Table2
SNO
SubsidaryName

What I'm trying to get to is the customer name based on the subsidiaries
If there are multiple subsidiaries in table 2 with different names I want the customer name to be from table 1
If there is only 1 subsidiary within table 2 I want the name to be from table 2
if there are multiple subsidiaries with the same name in table 2 then I want customer name from table 2

Table 1 Data
SNO CustomerName
1234 Alexir Group
2341 Corp USA
5432 Silicon Industries
7890 Panama Group
6590 Allaint Bank

Table 2 Data
SNO Customer Name
1234 Alexir Group 1
2341 Corp USA
2341 Corp USA
5432 Silicon Industries 1
5432 Silicon Industries 2
5432 Silicon Industries 3
7890 Panama Group
6590 Allaint Bank
6590 Allaint Bank
6590 Allaint Bank 1

I want the data to be returned as

SNo CustomerName
1234 Alexir Group 1
2341 Corp USA
5432 Silicon Industries
7890 Panama Group
6590 Allaint Bank

Any help is greatly appreciated.

Thank You in advance


#2

Hi jim

I Hope this will help

Declare @table1 table(SNO int , CustomerName varchar(50))

insert into @table1(SNO,CustomerName)
SELECT 1234, 'Alexir Group'
union all
SELECT 2341, 'Corp USA'
union all
SELECT 5432, 'Silicon Industries'
union ALL
SELECT 7890,'Panama Group'
union all
SELECT 6590, 'Allaint Bank'

Declare @table2 table(SNO int , CustomerName varchar(50))

insert into @table2(SNO,CustomerName)
SELECT 1234, 'Alexir Group 1'
UNION ALL
SELECT 2341, 'Corp USA'
UNION ALL
SELECT 2341, 'Corp USA'
UNION ALL
SELECT 5432, 'Silicon Industries 1'
UNION ALL
SELECT 5432, 'Silicon Industries 2'
UNION ALL
SELECT 5432, 'Silicon Industries 3'
UNION ALL
SELECT 7890, 'Panama Group'
UNION ALL
SELECT 6590, 'Allaint Bank'
UNION ALL
SELECT 6590, 'Allaint Bank'
UNION ALL
SELECT 6590, 'Allaint Bank 1'

Select * from @table1
SELECT * from @table2

Select Distinct t.sno,Case When rn = 1 then b.CustomerName Else t.CustomerName end from @table1 t JOIN (
SELECT count (A.SNO)
over ( partition by A.SNO ) rn,*
FROM (SELECT Distinct SNO,customerName from @table2) A ) b
ON t.SNO = b.SNO