how to write a select store proc for this using four or more table for report generating ?

• Sivaganesh Tamilvendhan

  Hall of Fame

  Points: 3498

  More actions

  May 3, 2012 at 2:14 pm

  #257509

  for example here i am using five table?

  table1

  catid categoryname

  1 cricket

  2 football

  table2

  sid catid(fk) subcatgoryname

  1 1 bowling

  2 1 batting

  3 1 fielding

  4 2 foul

  5 2 goal

  table3

  adforumid memberid (fk) adforumname activesstatus

  1 1 inform 1

  table4

  adinfid adforumid(fk) cid(fk) sid(fk)

  1 1 1 1

  2 1 1 2

  3 1 2 4

  table 5

  memberid membername firstname lastname

  1 john paul stepen

  2 andrew sam winson

  i just want to take report depend on table 3 activestatus in where condition like this

  DECLARE @Names VARCHAR(8000)

  SELECT

  @Names = COALESCE(@Names + ', ', '') + (CatId+ '/' +CatName

  + ','SubCatId+'|'+SubCatName,SubCatId|SubCatName,..]))

  from

  table4

  left outer join

  table1 on catid=catid

  left outer join

  table2 on subid=subid

  left outer join

  table3

  on adforumid=adforumid

  left outer join

  table5 on memberid=memberid

  where

  activestatus =1

  select

  adinfid

  ,membername

  ,memberid

  ,adforumname

  , @Names as names

  from

  table4

  left outer join

  table1 on catid=catid

  left outer join

  table2 on subid=subid

  left outer join

  table3

  on adforumid=adforumid

  left outer join

  table5 on memberid=memberid

  where

  activestatus =1

  in this one store proc iam expecting output

  [CatId1|CatName,SubCatId|SubCatName,SubCatId|SubCatName,..][CatId2|CatName,SubCatId|SubCatName,SubCatId|SubCatName,..][CatId2|CatName,SubCatId|SubCatName,SubCatId|SubCatName,..][CatId2|CatName,SubCatId|SubCatName,SubCatId|SubCatName,..]

  adinfid membername memberid adforumname Names

  1 john 1 inform [1| cricket,1| bowling,2| batting] [2|football,4|foul]

  can any one plz try to help me by solving this problem

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 3, 2012 at 2:25 pm

  #1483338

  sivag (5/3/2012)

  ---

  for example here i am using five table?

  table1

  cid categoryname

  1 cricket

  2 football

  table2

  sid cid(fk) subcatgoryname

  1 1 bowling

  2 1 batting

  3 1 fielding

  4 2 foul

  5 2 goal

  table3

  adforumid memberid (fk) adforumname activesstatus

  1 1 inform 1

  table4

  adinfid adforumid(fk) cid(fk) sid(fk)

  1 1 1 1

  2 1 1 2

  3 1 2 4

  table 5

  memberid membername firstname lastname

  1 john paul stepen

  2 andrew sam winson

  I just want to take report depend on table 3 activestatus in where condition like this

  DECLARE @Names VARCHAR(8000)

  SELECT

  @Names = COALESCE(@Names + ', ', '') + (CatId+ '/' +CatName

  + ','SubCatId+'|'+SubCatName,SubCatId|SubCatName,..]))

  from

  table4

  left outer join

  table1 on catid=catid

  left outer join

  table2 on subid=subid

  left outer join

  table3

  on adforumid=adforumid

  left outer join

  table5 on memberid=memberid

  where

  activestatus =1

  select

  adinfid

  ,membername

  ,memberid

  ,adforumname

  , @Names as names

  from

  table4

  left outer join

  table1 on catid=catid

  left outer join

  table2 on subid=subid

  left outer join

  table3

  on adforumid=adforumid

  left outer join

  table5 on memberid=memberid

  where

  activestatus =1

  In this one store proc I am expecting output

  [CatId1|CatName,SubCatId|SubCatName,SubCatId|SubCatName,..][CatId2|CatName,SubCatId|SubCatName,SubCatId|SubCatName,..][CatId2|CatName,SubCatId|SubCatName,SubCatId|SubCatName,..][CatId2|CatName,SubCatId|SubCatName,SubCatId|SubCatName,..]

  adinfid membername memberid adforumname Names

  1 john 1 inform [1| cricket,1| bowling,2| batting] [2|football,4|foul]

  Can any one plz try to help me by solving this problem

  Hope the above is a bit more readable.