Re: sql query help

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