Of course for greater efficiency ( depending on your indexes ) just
add the where clause as shown below...
SELECT orderid
FROM ( SELECT orderid,
MAX( CASE WHEN productid = 'p2' THEN 1 ELSE 0 END )
has_p2,
MAX( CASE WHEN productid = 'p5' THEN 1 ELSE 0 END )
has_p5
FROM yourtable
WHERE productid IN ( 'p2','p5 )
GROUP BY orderid
)
WHERE has_p2 = 1
AND has_p5 = 1
DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E66601C.581B170_at_exesolutions.com>...
> steve wrote:
>
> > DA Morgan wrote...
> > >>>>
> > >>>> i've done some looking but am also having problems with another query,
> the
> > >>>> same table:
> > >>>>
> > >>>> orderid
> > >>>> productid
> > >>>> qty
> > >>>>
> > >>>> say it has the data:
> > >>>>
> > >>>> o1,p2,q1
> > >>>> o1,p5,q1
> > >>>> o2,p3,q1
> > >>>> o3,p2,q1
> > >>>> o3,p5,q1
> > >>>>
> > >>>> how would i show the orderid or orders that contained p2 and p5
> > >>>>
> > >>>> i have tried a mixture of group by, and sub queries and also looked
> into
> > >>>> using 'having', i can display orders that have p2 or p5 but not orders
> that
> > >>>> have both in them, taken me 2 hours of playing and looking around so
> far
> > >>>> --
> > >>>> get me tools and beer
> > >>>
> > >>> Is this school work? If so we don't do homework unless we get the
> certificate.
> > >>> If this is work for an employer why aren't you asking a senior developer
> or
> > >>> your DBA?
> > >>>
> > >>> Daniel Morgan
> > >>
> > >> Its not homework, well it is as such but its for my own learning.
> > >>
> > >> I have worked through the introduction to oracle book (pl/sql) and tried
> the
> > >> exercises in there, and now im working through again using my own tables
> > >> instead of the ones in the book, as you can see from the table set up its
> > >> nothing advanced.
> > >>
> > >> I know you probably get quite a few on here asking things like this so I
> > >> understand. Just a pointer will be helpful, cheers
> > >>
> > >> --
> > >> get me tools and beer
> > >
> > > Ok then here's one approach.
> > >
> > > SELECT *
> > > FROM table
> > > WHERE orderid = 'P2'
> > >
> > > this gives you one set.
> > >
> > > SELECT *
> > > FROM table
> > > WHERE orderid = 'P5'
> > >
> > > gives you the other.
> > >
> > > Merge them together with UNION ALL
> > >
> > > SELECT *
> > > FROM table
> > > WHERE orderid = 'P2'
> > > UNION ALL
> > > SELECT *
> > > FROM table
> > > WHERE orderid = 'P5'
> > >
> > > Then query this result set as an in-line view.
> > >
> > > SELECT stuff
> > > FROM (
> > > SELECT *
> > > FROM table
> > > WHERE orderid = 'P2'
> > > UNION ALL
> > > SELECT *
> > > FROM table
> > > WHERE orderid = 'P5');
> > >
> > > It is not the only solution ... but it is one you should add to your
> skills.
> > >
> > > Daniel Morgan
> >
> > I tried what you suggested, but with more data in the table the sql above
> > gives me the orderid of products that have either P2 or P5 in the order
> >
> > The union and union all is new to me, i have had a look on the oracle site
> > but no luck, i can either get the orderid of p2 OR p5 or it selects no rows
> > at all. This has been pretty much what i have been getting all day.
> >
> > cheers for your help
> >
> > --
> > get me tools and beer
>
> Given that you are a student I am trying to help you ... not hand you the
> completed answer which is why I put 'SELECT stuff'.
>
> With a little modification to the basic structure I gave you you should be able
> to obtain the answer. Put some effort into it ... show what you have done ...
> and I, or othes, will guide you to the solution.
>
> I have a basic philosophical belief system that precludes handing completed
> solutions to those genuinely trying to learn.
>
> Daniel Morgan
Received on Thu Mar 06 2003 - 16:01:56 CST
