BEGIN
select id, mappedto, '4306' as cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME,status,entrydate
into [update20150723].[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 [update20150723].[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 [update20150723].[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 [update20150723].[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 [update20150723].[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 [update20150723].[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 [update20150723].[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 [update20150723].[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 [update20150723].[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 [update20150723].[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 [update20150723].[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 [update20150723].[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 [update20150723].[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', 'update20150723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Kualalumpur_test', 'update20150723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Pattaya_test', 'update20150723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Bangkok_test', 'update20150723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Milan_test', 'update20150723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_London_test', 'update20150723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Zurich_test', 'update20150723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_HongKong_test', 'update20150723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Phuket_test', 'update20150723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Lucerne_test', 'update20150723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_NewYork_test', 'update20150723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Istanbul_test', 'update20150723B'
go
[IbeCitySampleMappings].[dbo].[usp_removeDuplicateHotelMapping] 'tab_CitySampleMappings_Amsterdam_test', 'update20150723B'
go
----------------------------
/*
CREATE procedure [dbo].[usp_RemoveDuplicateGroups]
as
update update20150723A.dbo.tab_CitySampleMappings_Lucerne_test
set mappedto=b.mappedto
from update20150723A.dbo.tab_CitySampleMappings_Lucerne_test a inner join
(select id,mappedto from update20150723A.dbo.tab_CitySampleMappings_Lucerne_test
where id in
(
select mappedto from update20150723A.dbo.tab_CitySampleMappings_Lucerne_test
) and id>mappedto
) b on a.mappedto=b.id and a.id<>b.mappedto
update update20150723A.dbo.tab_CitySampleMappings_Lucerne_test
set mappedto=id
where id in
(
select mappedto
from update20150723A.dbo.tab_CitySampleMappings_Lucerne_test
where id in
(
select mappedto from update20150723A.dbo.tab_CitySampleMappings_Lucerne_test
) and id>mappedto)
*/
Thank you for reading the article about sql server Join Query on the blog NEW TECH If you want to disseminate this article on please list the link as the source, and if this article was helpful please bookmark this page in your web browser by pressing Ctrl + D on your keyboard keys.