http://computertriksno1.blogspot.in/

sql server Join Query

24 Jul 2013 1 comments


BEGIN
      select id, mappedto, '4306' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Paris_test]
      from
      (
      select id,mappedto,'4306' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyParis2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'Paris%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '2902' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Kualalumpur_test]
      from
      (
      select id,mappedto,'2902' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyKualalumpur2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'KUALA LUMPUR%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '9196' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Pattaya_test]
      from
      (
      select id,mappedto,'9196' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyPattaya2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'Pattaya%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '418' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Bangkok_test]
      from
      (
      select id,mappedto,'418' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyBangkok2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'Bangkok%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '3648' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Milan_test]
      from
      (
      select id,mappedto,'3648' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyMilan2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'Milan%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '3216' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_London_test]
      from
      (
      select id,mappedto,'3216' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyLondon2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'London%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '6353' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Zurich_test]
      from
      (
      select id,mappedto,'6353' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyZurich2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'Zurich%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '7063' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_HongKong_test]
      from
      (
      select id,mappedto,'7063' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyHongKong2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      (b.cityname like 'Hong Kong%' or b.cityname like 'HongKong%') and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '7652' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Phuket_test]
      from
      (
      select id,mappedto,'7652' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyPhuket2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'Phuket%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END


BEGIN
      select id, mappedto, '3271' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Lucerne_test]
      from
      (
      select id,mappedto,'3271' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyLucerne2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'Lucerne%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '9116' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_NewYork_test]
      from
      (
      select id,mappedto,'9116' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyNewYork2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'New York%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '2585' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Istanbul_test]
      from
      (
      select id,mappedto,'2585' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyIstanbul2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'Istanbul%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END



BEGIN
      select id, mappedto, '173' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
      into [update20130723].[dbo].[tab_CitySampleMappings_Amsterdam_test]
      from
      (
      select id,mappedto,'173' as cityid,'MATCH' as MAPCHECKSTATUS,'FUZZY_SYSTEM' as checkbyuser,getdate() as MAPCHECKTIME,'active' as status,getdate() as entrydate
      from [FuzzyMap2].[dbo].[FuzzyAmsterdam2$]
      where not id in (SELECT  [id] FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto)
      union all
      SELECT id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,'activee' as status,getdate() as entrydate FROM IbeCitySampleMappings.[dbo].tab_CitySampleMappings_new where id<>mappedto
      ) X where x.id in
      (
      select id from ibehotels.dbo.tab_hotels A
      inner join Ibedb.dbo.tab_masterCities B on a.citycode=b.CityCode and a.source=b.source
      where 
      b.cityname like 'Amsterdam%' and
      not b.source in
      ('AMADEUS',
      'ASIAN')
      )
END
go


-----===============================================================================

[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Paris_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Kualalumpur_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Pattaya_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Bangkok_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Milan_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_London_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Zurich_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_HongKong_test', 'update20130723B'
go




[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Phuket_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Lucerne_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_NewYork_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Istanbul_test', 'update20130723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Amsterdam_test', 'update20130723B'
go








----------------------------

/*




CREATE procedure [dbo].[usp_RemoveDuplicateGroups]   
as   
update update20130723A.dbo.tab_CitySampleMappings_Lucerne_test  
set mappedto=b.mappedto   
from update20130723A.dbo.tab_CitySampleMappings_Lucerne_test a inner join    
(select id,mappedto from update20130723A.dbo.tab_CitySampleMappings_Lucerne_test   
where id in   
(   
select mappedto from update20130723A.dbo.tab_CitySampleMappings_Lucerne_test   
 ) and id>mappedto   
 ) b on a.mappedto=b.id and a.id<>b.mappedto   
     
update update20130723A.dbo.tab_CitySampleMappings_Lucerne_test   
set mappedto=id   
where id in   
(    
select mappedto    
from update20130723A.dbo.tab_CitySampleMappings_Lucerne_test   
where id in   
(   
select mappedto from update20130723A.dbo.tab_CitySampleMappings_Lucerne_test   
 ) and id>mappedto)


*/




Share this article :
Posted by Girish at 11:06
Labels: , , , , ,

+ comments + 1 comments

Reply
Yoganand
19 August 2013 at 23:21

You can watch on YouTube => Click Here

!!!Demo!!! Nice one...
http://infonethub.blogspot.in/ !!!Demo!!!

Post a Comment

 
Support : GDDon | Creating Website | Gddon |
Copyright © 2013. Computer Tricks and Tips for System - All Rights Reserved
Template Created by Creating Website Modify by GDDon.Com
Proudly powered by Blogger