May 18, 2011 at 4:55 am
Hi All
Let me explain the scenario.
Table1 Having information Customer 1st Name and Table2 having Combination of Names.
Table1
KIM
JOE
TESTA
KELLY
AMAN
JULIOT
Table2
KIM JOE TESTA
AMAN JOE TESTA
JULIOT JOE TESTA
JULIOT SHAWN TESTA
SHAWN JULIOT TESTA
JULIOT TESTA SHAWN
Now i have to display the Name which is not present in Parent Table (Table1) for e.g.here SHAWN
Do i have to split 1st and search the name in Table1? then in this case for all the repeatation i have to perform search.
How can i best write my SQL logic to achive this ?
May 18, 2011 at 7:54 am
This sounds like homework. You will need a parse the names in table 2 to get all the appropriate names, then join it to table 1 to get what is missing. here's some code to get started.
declare @t table (name varchar(10))
declare @t1 table (name varchar(30))
insert into @t values
('KIM'),
('JOE'),
('TESTA'),
('KELLY'),
('AMAN'),
('JULIOT')
insert into @t1 values
('KIM JOE TESTA'),
('AMAN JOE TESTA'),
('JULIOT JOE TESTA'),
('JULIOT SHAWN TESTA'),
('SHAWN JULIOT TESTA'),
('JULIOT TESTA SHAWN')
select item from
(select distinct item from @t1
cross apply dbo.DelimitedSplit8K(name, ' ')) v
left outer join @t t
on v.Item = t.name
where t.name is null
The TVF DelimitedSplit8K was code provided by Jeff Moden in his article about tally tables that can be found here. The code is very useful and should be kept on hand at all times.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply