I have a table that contains 4 columns: entry_id, NAME, type, upper_id.

Each entry_id corresponds to a specific upper_id. There can be many different entry_id's that correspond to SAME upper_id. The column type represent the level of that entry in terms of hierarchy.

1 --> First level
2 --> Second level
3 --> Third level

I need to match what is in the upper_id to the entry_id in order to add another column for upper_name which will be found in the same table column name. In the example above, if upper_id = '1001' for an entry_id, I need to find this value in the entry_id and grab the name found.

An entry_id with type = '1' will always have an upper_id that has a value in the entry_id with a type = '2'. And an entry_id with a type = '2' will always have an upper id that has a value in entry_id with a type = '3'. An entry_id with type = '3' will never have an upper_id, and its upper_name column will be NULL.

The results will look like this:

CREATE TABLE TBL_1
(     ENTRY_ID int,
      NAME string,
      TYPE int,
      UPPER_ID int
);

INSERT INTO TBL_1
values
(1234,'bichon',1,'1001'),
(1124,'yorkie',1,'1001'),
(1009,'golden',1,'1001'),
(6722,'poodle',1,'1001'),
(1001,'canine',2,'2002'),
(2002,'animal',3,NULL)

Do you know how I can formulate a query that allows me to include an "upper_name" column from the data already in the table?

CodePudding user response：

LEFT JOIN is the key.

WITH table_data (ENTRY_ID, NAME, TYPE, UPPER_ID) AS (
  SELECT * 
  FROM values
    (1234,'bichon',1,1001),
    (1124,'yorkie',1,1001),
    (1009,'golden',1,1001),
    (6722,'poodle',1,1001),
    (1001,'canine',2,2002),
    (2002,'animal',3,NULL)
)
SELECT t1.*,
    t2.name as upper_name
FROM table_data AS t1
LEFT JOIN table_data AS t2
    ON t1.UPPER_ID = t2.ENTRY_ID
        AND t1.type  1 = t2.type

gives:

CodePudding user response：

left join the table with itself as following:

select 
t1.ENTRY_ID ,
t1.NAME,
t1.TYPE,
t1.UPPER_ID,
t2.NAME as upper_name

from TBL_1 as t1
left join TBL_1 as t2 on t1.UPPER_ID = t2.ENTRY_ID

The LEFT JOIN keyword returns all records from the left table (t1), and the matching records from the right table (t2),in case no matching record from the right table it will return null for its selected columns.

CodePudding user response：

Join the table to itself on upper_id = entry_id and type = type - 1, then union all with the type 3 rows.

SELECT x.*, y.NAME as upper_name
FROM tbl_1 x
    inner join tbl_1 y on x.UPPER_ID = y.ENTRY_ID and x.TYPE = y.TYPE - 1
WHERE x.TYPE IN (1,2)
UNION ALL
SELECT *, NULL AS upper_name
FROM TBL_1
WHERE type = 3;