Re: sql query help

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Wed, 05 Mar 2003 09:46:59 -0800
Message-ID: <3E663813.8F2257BF@exesolutions.com>

steve wrote:

> DA Morgan wrote...
> > steve wrote:
> >
> >> steve wrote...
> >>> DA Morgan wrote...
> >>>> steve wrote:
> >>>>
> >>>>> hi peeps
> >>>>>
> >>>>> i have a table set up
> >>>>>
> >>>>> table: orderitems
> >>>>> orderid
> >>>>> productid
> >>>>> qty
> >>>>>
> >>>>> is there anyway of listing productid of items ordered x times?
> >>>>>
> >>>>> i have managed to get basic things working but found no reference to
> this
> >>>>> and not sure if the set up of my database would allow such a query
> >>>>>
> >>>>> cheers :)
> >>>>>
> >>>>> --
> >>>>> get me tools and beer
> >>>>
> >>>> There is indeed.
> >>>>
> >>>> SELECT productid, COUNT(*)
> >>>> FROM orderitems
> >>>> GROUP BY productid
> >>>> HAVING COUNT(*) = x;
> >>>>
> >>>> Go to http://tahiti.oracle.com and search on GROUP BY.
> >>>>
> >>>> Daniel Morgan
> >>>
> >>> Thanks very much.
> >>>
> >>> I didnt know it was possible, done a search on google gave me loads of
> other
> >>> stuff and I didnt have any idea how to word the search. Never occured to
> me
> >>> to check the oracle site!
> >>>
> >>> Thanks again :)
> >>
> >> 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 Received on Wed Mar 05 2003 - 11:46:59 CST