Parent-Child Grouping

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  May 12, 2014 at 1:12 pm

  #307304

  Hi,

  below is my table structure

  Create table ParentMember(IdParentMember int,Name varchar(40),company varchar(40))

  insert into ParentMember values (1,'Andrew','Microsoft'),

  (1,'Rob','IBM'),

  (1,'bob','SunMicro'),

  (1,'Peter','Oracle')

  Create table ChildMember(IdchildMember int,Name varchar(40),company varchar(40))

  insert into ChildMember values (100,'Andrew','Microsoft'),

  (101,'Blake','Microsoft'),

  (102,'Rob','IBM'),

  (103,'stanlye','IBM'),

  (104,'bob','SunMicro'),

  (105,'Peter','Oracle')

  (103,'Peter','Oracle')

  Required output:

  select 1 as IdParentMember,100 as IdchildMember,'Microsoft' as company

  select 1 as IdParentMember,101 as IdchildMember,'Microsoft' as company

  select 2 as IdParentMember,101 as IdchildMember,'IBM' as company

  select 2 as IdParentMember,102 as IdchildMember,'IBM' as company

  select 3 as IdParentMember,102 as IdchildMember,'SunMicro' as company

  select 4 as IdParentMember,103 as IdchildMember,'Oracle' as company

  The condition will be mapping on Company

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 12, 2014 at 1:47 pm

  #1712840

  KGJ-Dev (5/12/2014)

  ---

  Hi,

  below is my table structure

  Create table ParentMember(IdParentMember int,Name varchar(40),company varchar(40))

  insert into ParentMember values (1,'Andrew','Microsoft'),

  (1,'Rob','IBM'),

  (1,'bob','SunMicro'),

  (1,'Peter','Oracle')

  Create table ChildMember(IdchildMember int,Name varchar(40),company varchar(40))

  insert into ChildMember values (100,'Andrew','Microsoft'),

  (101,'Blake','Microsoft'),

  (102,'Rob','IBM'),

  (103,'stanlye','IBM'),

  (104,'bob','SunMicro'),

  (105,'Peter','Oracle')

  (103,'Peter','Oracle')

  Required output:

  select 1 as IdParentMember,100 as IdchildMember,'Microsoft' as company

  select 1 as IdParentMember,101 as IdchildMember,'Microsoft' as company

  select 2 as IdParentMember,101 as IdchildMember,'IBM' as company

  select 2 as IdParentMember,102 as IdchildMember,'IBM' as company

  select 3 as IdParentMember,102 as IdchildMember,'SunMicro' as company

  select 4 as IdParentMember,103 as IdchildMember,'Oracle' as company

  The condition will be mapping on Company

  You need to validate your sample data. From what I see the value for IdParentMember is 1 even though your expected results shows values from 1 to 4.