Re: sql query help

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