CODEWITHSUNDEEP
sqlsql-server
Meline
Meline

Reputation: 119

How insert parent and child record in one column in sql?

I need to put the parent child records in one column to other table with that ids. and I tried this:

select parent.Parent,child1.child1,Child2.child2 
from parent 
join Child1 on child1.ParentIdId=parent.ParentID 
join Child2  on child1.child1Id=child2.child1Id` 

Create table parent (ParentID int, Parent varchar(10))

Create table Child1 (child1Id int, child1 varchar(10), ParentIdId int)
Create table Child2 (child2Id int, child2 varchar(10), child1Id int)


insert into parent values(10,'Sony'),(20,'Apple'),(30,'HTC'),(40,'Nexus')
insert into Child1 values(100,'Sony1',10),(200,'Sony2',10),(300,'Apple1',20),(400,'Apple2',20),(500,'HTC1',30),(600,'HTC2',30),
(700,'Nexus1',40),(800,'Nexus2',40)

insert into Child2 values(1000,'Sony11',100),(2000,'Sony22',100),(3000,'Apple11',200),(4000,'Apple22',200),(5000,'HTC11',300),(6000,'HTC22',300),
(7000,'Nexus11',400),(8000,'Nexus22',400)

Output I need:

Ids Products    Parents
10  Sony        null
20  Apple       null
30  HTC         null
40  Nexus       null
100 Sony1       10
200 Sony2       10
300 Apple1      20
400 Apple2      20
500 HTC1        30
600 HTC2        30
700 Nexus2      40
800 Nexus2      40
1000    Sony11  100
2000    Sony22  100
3000    Apple11 200
4000    Apple22 200
5000    HTC11   300
6000    HTC22   300
7000    Nexus11 400
8000    Nexus22 400

Upvotes: 0

Views: 471

Answers (3)

Sathyajith
Sathyajith

Reputation: 4024

Without using union all and multiple select statements 

SELECT COALESCE(parent.ParentID, Child1.child1id, Child2.child2id),
       COALESCE(Child2.child2, Child1.child1, parent.Parent), 
       COALESCE( Child1.ParentIdId, Child2.child1id) FROM parent
FULL JOIN Child1 on 1 = 2
FULL JOIN Child2 on 1 = 2

Upvotes: 2

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

I think for your example simple union all is suffice but i think you are looking for recursive cte as below:

;with cte (Child, nam, parent) as
(
    select * from child2
    union all
    select * from child1
    union all
    select *, null as Parent from parent
) 
, cte2 as
(
    select *, 0 as Levl from cte where parent is null

    union all

    select c1.*, c2.Levl + 1 as Levl from cte2 c2 join cte c1
        on c2.child = c1.parent
)
select * from cte2
order by levl

I have added Levl just to understand hierarchy

Upvotes: 1

Sathyajith
Sathyajith

Reputation: 4024

You probably has to do it in 3 different select query like below

SELECT ParentIds as Ids,
       Parent as Products,
       CAST(NULL AS INT) AS Parent
INTO #Product
FROM Parent
UNION ALL
SELECT child1.child1id,
       Child1.child1,
       Child1.ParentId 
FROM parent 
INNER JOIN Child1 on child1.ParentIdId=parent.ParentID 
UNION ALL
SELECT Child2.Child2Id,child2.child2,Child2.child1Id 
FROM parent 
INNER JOIN Child1 on child1.ParentIdId=parent.ParentID 
INNER JOIN Child2  on child1.child1Id=child2.child1Id`

Upvotes: 1

Related Questions