Shubham Vashishtha Shubham Vashishtha - 5 months ago 10
SQL Question

How should I join to achieve this

I am in kind of situation where I don't know where to go. I have to write a query such that it is kind of hierarchical.

I will explain you with an example-

Table A

Here we have 3 columns like 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 we have 4 columns like 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 we have to join these two tables on a hierarchical level like -

If the two Country matches, their states matches and their City matches than Number assigned to them

If two Country matches, their states matches and their City doesn't match (or it is null in another table) then we have to assign that Number from Table B whereby joining those two tables city will be null and other two column match

It is like a hierarchy first we matach till the lowest granuality level, if that matches its ok if not go to one level up and like this.

If only the two country matches than we have to assign the number where other two columns are null.

My output for the above two will be like 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 I have a approch to achieve this also i.e.,

First I inner join both of them and goal column will be 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 I will join on the basis of Country and State and with condition

that goal should not filled in Table A and City is null in Table B.

Which will 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 I will join on country basis and where goal is empty in table A and state is null in table B

which we be like 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 is what I want eventually

Now a problem with this approch is it based on update. But here I have to do select.

How to write this in sql. That is my problem.

Please can anyone help me out here.

Answer

It can be done easily using several LEFT JOINS on different criteria:

DECLARE @Countries TABLE
(
    [Country] VARCHAR(12)
   ,[State] VARCHAR(16)
   ,[City] VARCHAR(16)
);

INSERT INTO @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 INTO @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]) AS [Number]
FROM @Countries C
LEFT JOIN @CountriesCodes CC1
    ON C.[Country] = CC1.[Country]
    AND C.[State] = CC1.[State]
    AND C.[City] = CC1.[City]
LEFT JOIN @CountriesCodes CC2
    ON C.[Country] = CC2.[Country]
    AND C.[State] = CC2.[State]
    AND CC2.[City] IS NULL
LEFT JOIN @CountriesCodes CC3
    ON C.[Country] = CC3.[Country]
    AND CC3.[State] IS NULL
    AND CC3.[City] IS NULL;

enter image description here

Now, let's say there is additional column Number in the first table which you want to update (I guess this is what you want):

UPDATE @Countries 
SET [Number] = COALESCE(CC1.[Number], CC2.[Number], CC3.[Number])
FROM @Countries C
LEFT JOIN @CountriesCodes CC1
    ON C.[Country] = CC1.[Country]
    AND C.[State] = CC1.[State]
    AND C.[City] = CC1.[City]
LEFT JOIN @CountriesCodes CC2
    ON C.[Country] = CC2.[Country]
    AND C.[State] = CC2.[State]
    AND CC2.[City] IS NULL
LEFT JOIN @CountriesCodes CC3
    ON C.[Country] = CC3.[Country]
    AND CC3.[State] IS NULL
    AND CC3.[City] IS NULL;
Comments