How to get parent_id using mysql query?

Question

If my SQL query is like this :

SELECT * FROM master_lookups WHERE `type` = 'fungsi'`, 

then the result is like this:

(image 1)

If my SQL query is like this :

SELECT @rownum := @rownum + 1 AS id,
       NULL AS parent_id,
       kdsfungsi AS `code`,
       nmsfungsi AS `name`,
       'sub_fungsi' AS `type`, 
       CONCAT('{"kdfungsi":"', kdfungsi, '"}') AS information 
FROM   dblaplakgar.t_sfungsi 
CROSS JOIN (SELECT @rownum := 735) r

then the result is like this:

(image 2)

I want the result of image 2, there exists the value parent_id. The value of field parent_id is taken from the id in the image 1, based on the field kdfungsi.

So as regards the value parent_id in image 2, the result is like this:

• id 736, its parent id: 735
• id 737, its parent id: 735
• id 738, its parent id: 734

Once I get parent_id, I use this query to insert:

INSERT INTO master_lookups (id,parent_id,`code`,`name`,`type`,information)
SELECT @rownum := @rownum + 1 AS id,
       NULL AS parent_id,
       kdsfungsi AS `code`,
       nmsfungsi AS `name`,
       'sub_fungsi' AS `type`, 
       CONCAT('{"kdfungsi":"', kdfungsi, '"}') AS information
FROM   dblaplakgar.t_sfungsi 
CROSS JOIN (SELECT @rownum := 735) r

There are parent_id values that are still NULL.

I need to fill the parent_id on image 2 based on the field kdfungsi as seen on image 1.

How can I do that?

Answer 1

To get the id value from the master_lookups table, you could do this:

SELECT     @rownum := @rownum + 1 AS id,
           m.id AS parent_id,
           t.kdsfungsi AS `code`,
           t.nmsfungsi AS `name`,
           'sub_fungsi' AS `type`, 
           CONCAT('{"kdfungsi":"', t.kdfungsi, '"}') AS information 
FROM       dblaplakgar.t_sfungsi t 
LEFT JOIN  master_lookups m
       ON  m.type = 'fungsi'
       AND m.code = t.kdfungsi
CROSS JOIN (SELECT @rownum := 735) r