Query to one parent record from multiple child record

Kihiy352 · May 12, 2024, 7:22pm

Hi, Thanks in advance,I need help in creating SQL query to generate report to get one parent account for multiple child record( db - postgres db ).

example below with below rules

For each "Id" consider lowest "sub_id" as parent_id and other as child_id
The parent_id shoul be same for all child_id
I.e. no child_id should have multiple parent_id
The parent_id and child_id can't be same

Id sub_id
1 a11
1 a12
2 a13
2 a16
2 a17
2 a14
3 a31
4 a32
4 a33
4 a35

Final result
Parent_id child_id
a11 a12
a13 a14
a13 a16
a13 a17
a32 a33
a32 a35

harishgg1 · February 22, 2025, 10:39pm

hi

hope this helps

create data script

Drop Table test_table
-- Create the table
CREATE TABLE test_table (
Id INT,
sub_id VARCHAR(10)
);

-- Insert the data
INSERT INTO test_table (Id, sub_id)
VALUES
(1, 'a11'),
(1, 'a12'),
(2, 'a13'),
(2, 'a16'),
(2, 'a17'),
(2, 'a14'),
(3, 'a31'),
(4, 'a32'),
(4, 'a33'),
(4, 'a35');

;WITH parent_ids AS (  
    SELECT Id, MIN(sub_id) AS parent_id  
    FROM test_table  
    GROUP BY Id  
)  
SELECT p.parent_id AS Parent_id, t.sub_id AS child_id  
FROM test_table t  
JOIN parent_ids p ON t.Id = p.Id  
WHERE t.sub_id != p.parent_id  
ORDER BY p.parent_id;