sql - How should I join to achieve this -

i in kind of situation don't know go. have write query such kind of hierarchical.

i explain example-

table a

here have 3 columns this

  country      state          city         india      punjab         amristar        india      punjab         ludhiana       india     tamil nadu      chennai            india     tamil nadu       salem     india     west bengal     kolkata    india     west bengal     darjeeling   india     maharastra      mumbai                              india     maharastra      nagpur 

table b

here have 4 columns this

country      state          city         number   india      punjab       amristar         3   india      punjab       null             5   india     tamil nadu    chennai          2       india     tamil nadu      null           4   india      null           null           6 

now have join these 2 tables on hierarchical level -

if 2 country matches, states matches , city matches number assigned them

if 2 country matches, states matches , city doesn't match (or null in table) have assign number table b whereby joining 2 tables city null , other 2 column match

it hierarchy first matach till lowest granuality level, if matches ok if not go 1 level , this.

if 2 country matches have assign number other 2 columns null.

my output above 2 this-

country        state          city             number   india       punjab        amristar              3         india       punjab        ludhiana              5   india     tamil nadu      chennai               2   india     tamil nadu       salem                4   india     west bengal     kolkata               6   india     west bengal     darjeeling            6   india     maharastra      mumbai                6                         india     maharastra      nagpur                6 

now have approch achieve i.e.,

first inner join both of them , goal column filled like-

country       state          city            number   india       punjab        amristar           3         india       punjab        ludhiana              india     tamil nadu      chennai            2   india     tamil nadu       salem                          india     west bengal     kolkata             india     west bengal     darjeeling                    india     maharastra      mumbai                                  india     maharastra      nagpur 

now join on basis of country , state , condition

that goal should not filled in table , city null in table b.

which give me this-

country       state          city            number   india       punjab        amristar           3         india       punjab        ludhiana           5      india     tamil nadu      chennai            2   india     tamil nadu       salem             4              india     west bengal     kolkata             india     west bengal     darjeeling                    india     maharastra      mumbai                                  india     maharastra      nagpur 

now join on country basis , goal empty in table , state null in table b

which this-

   country       state          city             number       india       punjab        amristar           3             india       punjab        ludhiana           5          india     tamil nadu      chennai            2       india     tamil nadu       salem             4                  india     west bengal     kolkata            6       india     west bengal     darjeeling         6              india     maharastra      mumbai             6                        india     maharastra      nagpur             6 

this want eventually

now problem approch based on update. here have select.

how write in sql. problem.

please can me out here.

it can done using several left joins on different criteria:

declare @countries table (     [country] varchar(12)    ,[state] varchar(16)    ,[city] varchar(16) );  insert @countries ([country], [state], [city]) values ('india', 'punjab', 'amristar')       ,('india', 'punjab', 'ludhiana    ')       ,('india', 'tamil nadu', 'chennai  ')       ,('india', 'tamil nadu', 'salem  ')       ,('india', 'west bengal', 'kolkata ')       ,('india', 'west bengal', 'darjeeling')       ,('india', 'maharastra', 'mumbai')       ,('india', 'maharastra', 'nagpur');  declare @countriescodes table (     [country] varchar(12)    ,[state] varchar(16)    ,[city] varchar(16)    ,[number] tinyint );  insert @countriescodes ([country], [state], [city], [number]) values ('india', 'punjab', ' amristar', '3')     ,('india', 'punjab', null, '5')     ,('india', 'tamil nadu', 'chennai', '2')     ,('india', 'tamil nadu', null, '4')     ,('india', null, null, '6');  select c.[country]       ,c.[state]       ,c.[city]       ,coalesce(cc1.[number], cc2.[number], cc3.[number]) [number] @countries c left join @countriescodes cc1     on c.[country] = cc1.[country]     , c.[state] = cc1.[state]     , c.[city] = cc1.[city] left join @countriescodes cc2     on c.[country] = cc2.[country]     , c.[state] = cc2.[state]     , cc2.[city] null left join @countriescodes cc3     on c.[country] = cc3.[country]     , cc3.[state] null     , cc3.[city] null; 

now, let's there additional column number in first table want update (i guess want):

update @countries  set [number] = coalesce(cc1.[number], cc2.[number], cc3.[number]) @countries c left join @countriescodes cc1     on c.[country] = cc1.[country]     , c.[state] = cc1.[state]     , c.[city] = cc1.[city] left join @countriescodes cc2     on c.[country] = cc2.[country]     , c.[state] = cc2.[state]     , cc2.[city] null left join @countriescodes cc3     on c.[country] = cc3.[country]     , cc3.[state] null     , cc3.[city] null;