combine queries RSS

• • Reply
  

  vik_16

  Member

  57 Points

  54 Posts

  combine queries

  Jul 28, 2012 08:38 PM|LINK

  Hi guys please help me on this senario

  select tb.* from (select tb1.* ,cm.Category_name from(select MPT.Membership_plan,AM.AgeGroup ,MPT.Category_id,PM.PaymentTerms,MPT.P_Termsecuritydeposit,MPT.P_Termregfee,MPT.P_Termplanfee
  from Membership_Payment_Terms MPT
  inner join Payment_Validity_Master PM
  on MPT.P_Term=PM.PaymentTermId
  inner join AgeMaster AM
  on MPT.AgeGroup_Id=Am.AgeId
  where MPT.Store_Id=3 and MPT.MemberShipStatus=1 and Pm.Status=1 and Am.AgeStatus=1)tb1

  select @list=category_id from Membership_Payment_Terms where membership_id=tb1.Category_id

  select Category_name from categorymaster where category_id in (select s from dbo.Split(',',@list)))tb

  How can i  get this  in single query

  • Edited by vik_16 on Jul 28, 2012 08:39 PM
• • Reply
  

  KumarHarsh

  All-Star

  15155 Points

  3658 Posts

  Re: combine queries

  Jul 29, 2012 05:51 AM|LINK

   db design and requirement is not clear.also output expected ,some sample data is nor known.

  in such scenerio,often cte is very helpful.something like this ?

  with CTE as
  (select MPT.Membership_plan,AM.AgeGroup ,MPT.Category_id,PM.PaymentTerms,MPT.P_Termsecuritydeposit,MPT.P_Termregfee,MPT.P_Termplanfee
  from Membership_Payment_Terms MPT
  inner join Payment_Validity_Master PM
  on MPT.P_Term=PM.PaymentTermId
  inner join AgeMaster AM
  on MPT.AgeGroup_Id=Am.AgeId
  where MPT.Store_Id=3 and MPT.MemberShipStatus=1 and Pm.Status=1 and Am.AgeStatus=1),
  cte1 as
  (select category_id from Membership_Payment_Terms inner join cte on membership_id=Category_id),
  cte2 as
  (select Category_name from categorymaster where category_id in (select s from dbo.Split(select category_id from cte1)))

  Warm Regards
  Kumar Harsh
  
  • Marked as answer by Chen Yu - MSFT on Aug 01, 2012 07:00 AM