I have a table that contains 4 columns:

1. primaryID,
2. code_value
3. type_section
4. parent_id

I need to lookup the value in the parent_id and match it with the primaryID in order to get the code_value for that parentID. For example, if the parent_id = '3', then I need to find the value 3 in the primaryID and get its code value.

If the parent_ID is NULL, then the code_value of that parent_ID should also be NULL since there is no matching value in the primaryID.

create table TBL_1 (
primaryid int,
code_value string,
type_section string,
parent_id string)

  insert into TBL_1 values
  (1,'12fsdd3','bichon',3),
  (2,'32llsdf','golden',3),
  (3,'32llsdf','dog',NULL),
  (4,'pp11222','cat',NULL),
  (5,'temm321','whisker',4),
  (6,'ph3m111','garfield',4)

Final table should look something like this:

Do you know how I can formulate a query to give these result?

CodePudding user response：

select t2.primaryid, 
       t2.code_value, 
       t2.type_section, 
       t1.primaryid as parent_id, 
       t1.code_value as parent_codevalue 
from tbl_1 t1 right join tbl_1 t2 on t1.primaryid = t2.parent_id;

CodePudding user response：

The stand way to do an optional join is the LEFT JOIN where the first table is always present but the right hand side does not have to match (as compared to a RIGHT JOIN where the right hand side is always present, with optional left)

SELECT a.*
    , b.code_value as parent_value 
FROM tbl_1 as a
LEFT JOIN tbl_1 as b 
    ON a.parent_id = b.primaryid;

gives: