July 7, 2010 at 9:24 am
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.
July 8, 2010 at 10:28 am
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
July 8, 2010 at 10:40 am
Cool It worked.
Thanks Jeff.
Thanks.
Gunjan.
July 8, 2010 at 2:29 pm
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.
July 8, 2010 at 2:34 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy