Get FormIDs which has combination of specific products

• Gunjan

  Hall of Fame

  Points: 3226

  More actions

  July 7, 2010 at 9:24 am

  #223618

  Hi,

  I need to get the results where FormIDs (i.e. OrderID) has specific set or combination of products.

  so it's like I need to know get the forms/orders which has the product group id (1,3) and 6.

  product id 1 and 3 is in the same group and product id 6 is in different group.

  So want to which order has both the product groups?

  Can any one help me on this?

  Here is my query

  SELECT BI.BRCFORMID FROM BRCFORMITEM BI

  inner join Lookupproducts L

  on BI.productid = L.NDC

  and L.Groupid in (1,3,6)

  its not giving me desired result. I need those orders which has group id either (1 or 3) and 6.

  Thanks in advance.

  Thanks.

  Gunjan.

• jjtetzlo

  SSCrazy

  Points: 2511

  More actions

  July 8, 2010 at 10:28 am

  #1190364

  I think I understand your requirements, but some sample data and expected output would have helped. Either way, I took a stab at it.

  create table #Lookupproducts (NDC int, Groupid int)

  create table #BRCFORMITEM (BRCFORMID int, productid int)

  insert into #Lookupproducts

  select 11, 1 union all

  select 12, 3 union all

  select 13, 5 union all

  select 14, 6

  insert into #BRCFORMITEM

  select 1234, 11 union all

  select 1234, 14 union all

  select 2345, 12 union all

  select 3456, 13 union all

  select 4567, 14 union all

  select 5678, 12 union all

  select 5678, 14

  ;with Groups as

  (

  select

  BRCFORMID,

  max(case when Groupid = 1 then 1 else null end) as 'Group1',

  max(case when Groupid = 3 then 1 else null end) as 'Group3',

  max(case when Groupid = 6 then 1 else null end) as 'Group6'

  from #BRCFORMITEM BI

  inner join #Lookupproducts L

  on BI.productid = L.NDC

  and L.Groupid in (1,3,6)

  group by

  BI.BRCFORMID

  )

  select BRCFORMID

  from Groups

  where (Group1 = 1 OR Group3 = 1) AND Group6 = 1

  drop table #Lookupproducts

  drop table #BRCFORMITEM

  - Jeff

• Gunjan

  Hall of Fame

  Points: 3226

  More actions

  July 8, 2010 at 10:40 am

  #1190373

  Cool It worked.

  Thanks Jeff.

  Thanks.

  Gunjan.

• Greg Snidow

  SSCoach

  Points: 16106

  More actions

  July 8, 2010 at 2:29 pm

  #1190497

  Gunjan (7/7/2010)

  ---

  Hi,

  SELECT BI.BRCFORMID FROM BRCFORMITEM BI

  inner join Lookupproducts L

  on BI.productid = L.NDC

  and L.Groupid in (1,3,6)

  I think you want to change this to...

  SELECT BI.BRCFORMID FROM BRCFORMITEM BI

  inner join Lookupproducts L

  on BI.productid = L.NDC

  WHERE L.Groupid in (1,3,6) --<< changed this

  Greg
  _________________________________________________________________________________________________
  The glass is at one half capacity: nothing more, nothing less.

• Gunjan

  Hall of Fame

  Points: 3226

  More actions

  July 8, 2010 at 2:34 pm

  #1190500

  Hey Greg,

  Thanks for the reply.

  I had already tried that query but it didn't worked.

  Jeff's solution worked for me.

  I appreciate your help in this.

  Thanks.

  Gunjan.