Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: sql query help

Re: sql query help

From: steve <jerrysinclair_at_hotmail.com>
Date: Wed, 5 Mar 2003 19:00:14 -0000
Message-ID: <HRr9a.10261$Vx2.781082@wards>


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
Received on Wed Mar 05 2003 - 13:00:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US