Re: sql query help

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Wed, 05 Mar 2003 09:01:20 -0800
Message-ID: <3E662D60.ED9CB479@exesolutions.com>

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 Received on Wed Mar 05 2003 - 11:01:20 CST