Re: sql query help

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Wed, 05 Mar 2003 12:37:48 -0800
Message-ID: <3E66601C.581B170@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 Wed Mar 05 2003 - 14:37:48 CST