So I currently have three tables in SQL:

Table One

Level 1 Hierarchy

Level 2 Hierarchy

I'm trying to find an SQL query to loop through both hierarchy tables to fill table one like below. I've thought about using LIKE but there are too many hierarchy codes to manually go through them like that.

CodePudding user response：

There are Many ways we can do this you can use direct update as well or Use Cursor for the Select statement as follows and Update the 2 nd table

DECLARE 
    @product_name VARCHAR(MAX), 
    @list_price   DECIMAL;

DECLARE cursor_product CURSOR
FOR SELECT 
        product_name, 
        list_price
    FROM 
        production.products;

OPEN cursor_product;

FETCH NEXT FROM cursor_product INTO 
    @product_name, 
    @list_price;

WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @product_name   CAST(@list_price AS varchar);
        FETCH NEXT FROM cursor_product INTO 
            @product_name, 
            @list_price;
      Update the table you want
    END;

CLOSE cursor_product;

CodePudding user response：

Try this:

/*
WITH 
  T1 (Name) AS
  (
    VALUES
      'Apple'               
    , 'Avacado'                 
    , 'Cabbage'
    , 'Cheese'
  )
, L1 (Code, Description) AS
  (
    VALUES
      ('A...', 'Fruit')
    , ('C...', 'Food')
  )
, L2 (Code, Description) AS
  (
    VALUES
      ('Ap..', 'Seed Fruit')
    , ('Av..', 'Stone Fruit')
    , ('Ca..', 'Vegetable')
    , ('Ch..', 'Dairy')
  )
*/
SELECT 
  T1.Name
, L1.Code           AS Code1
, L1.Description    AS Description1
, L2.Code           AS Code2
, L2.Description    AS Description2
FROM T1
JOIN L1 ON T1.Name LIKE TRANSLATE (L1.Code, '', '.', '') || '%'
JOIN L2 ON T1.Name LIKE TRANSLATE (L2.Code, '', '.', '') || '%'