Group by Parent with one child and multiple child information

• chethanmalur

  SSC Rookie

  Points: 27

  More actions

  July 25, 2014 at 1:10 pm

  #397119

  Basically i have three Tables

  Request ID Parent ID Account Name Addresss

  1452 1254789 Wendy's Atlanta Georgia

  1453 1254789 Wendy's Norcross Georgia

  1456 1254789 Waffle House Atlanta Georgia

  Bid_ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E Request_ID Parent ID

  45897 Incentive 10 N/A N/A N/A 1452 1254789

  45898 Incentive 10 N/A N/A N/A 1453 1254789

  45899 Incentive 10 N/A N/A N/A 1456 1254789

  Bid_Number Bid_Name Request_ID Parent ID

  Q789456 Wendy'Off 1452 1254789

  Q789457 Wendy'Reba 1452 1254789

  Q789456 Wendy'Off 1453 1254789

  Q789457 Wendy'Reba 1453 1254789

  Q789456 Wendy'Off 1456 1254789

  I want the Result

  Parent ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E AutoGeneratedCol

  1254789 Incentive 10 N/A N/A N/A 1

  1254789 Incentive 10 N/A N/A N/A 2

  Bid Number AutoGeneratedCol_Link

  Q789456 1

  Q789457 1

  Q789456 2

  Request ID AutoGeneratedCol_Link

  1452 1

  1453 1

  1456 2

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 25, 2014 at 1:29 pm

  #1732516

  Welcome to the forums.

  It's considered a nice way to ask for help if you post your data in a consumable format. As you're new, I did it for you, but I won't do it again as it's time consuming.

  CREATE TABLE #Requests(

  RequestID int,

  ParentID int,

  AccountName varchar(50),

  Addresss varchar(50))

  INSERT INTO #Requests

  VALUES

  (1452, 1254789, 'Wendy''s', 'Atlanta Georgia' ),

  (1453, 1254789, 'Wendy''s', 'Norcross Georgia'),

  (1456, 1254789, 'Waffle House', 'Atlanta Georgia')

  CREATE TABLE #Bids(

  Bid_ID int,

  Bid_Type varchar(50),

  Bid_Volume int,

  Bid_V varchar(50),

  Bid_D varchar(50),

  Bid_E varchar(50),

  RequestID int,

  ParentID int)

  INSERT INTO #Bids

  VALUES

  (45897, 'Incentive', 10, 'N/A', 'N/A', 'N/A', 1452, 1254789),

  (45898, 'Incentive', 10, 'N/A', 'N/A', 'N/A', 1453, 1254789),

  (45899, 'Incentive', 10, 'N/A', 'N/A', 'N/A', 1456, 1254789)

  CREATE TABLE #BidsRequests(

  Bid_Number varchar(50),

  Bid_Name varchar(50),

  RequestID int,

  ParentID int)

  INSERT INTO #BidsRequests

  VALUES

  ('Q789456', 'Wendy''Off', 1452, 1254789),

  ('Q789457', 'Wendy''Reba', 1452, 1254789),

  ('Q789456', 'Wendy''Off', 1453, 1254789),

  ('Q789457', 'Wendy''Reba', 1453, 1254789),

  ('Q789456', 'Wendy''Off', 1456, 1254789)

  Now, I'm not sure how do you want to get to your desired results. Could you elaborate some more on what do you want? Are you sure the results are correct?

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2