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

Home -> Community -> Usenet -> c.d.o.server -> Re: Subquery Question

Re: Subquery Question

From: <fitzjarrell_at_cox.net>
Date: 18 Feb 2005 06:48:52 -0800
Message-ID: <1108738132.199172.145690@o13g2000cwo.googlegroups.com>

fitzjarrell_at_cox.net wrote:
> Paul Izzo wrote:
> > Dieter your right syntax wise but it's not exactly what I'm looking
> > for. I'm looking basically to put 2 queries together in one. I've
> > done this before in the past but I can get it to work this time for
> me.
> >
> >
> > I have 2 queries that I'm looking to join. The first being:
> >
> > select ARBEITSPLAN_NR
> > from w100.papp
> > where papp.BELEGUNGSEINHEIT_NR in (3400,3500,3430)
> >
> > From this query I get 15,258 records.
> >
> > The second query that I need also is :
> >
> > select papp.ARBEITSPLAN_NR
> > from w100.papp
> > group by ARBEITSPLAN_NR
> > having count (papp.ARBEITSPLAN_NR) = 3
> >
> > From this query I get 4794 records. I'd like to combine the 2
> > queries to run together as 1.
> >
> > What I'm looking for is a ARBEITSPLAN_NR that contains only "3"
> > BELEGUNGSEINHEIT_NR from only the following (3400,3500,3430) and no
> > other.
> >
> > With the query that I get the 15,258 records I get results like
> > this:
> >
> > ARBEITSPLAN_NR = 123456
> > BELEGUNGSEINHEIT_NR = 3400
> > BELEGUNGSEINHEIT_NR = 9874
> > BELEGUNGSEINHEIT_NR = 3500
> >
> > What's good about this query is that I get "ARBEITSPLAN_NR" with
> only
> > 3 BELEGUNGSEINHEIT_NR but they contain only 1 or any combination of
> > BELEGUNGSEINHEIT_NR in (3400,3500,3430)
> >
> > What I'm looking for is something like this:
> >
> > ARBEITSPLAN_NR = 123456
> > BELEGUNGSEINHEIT_NR = 3400
> > BELEGUNGSEINHEIT_NR = 3500
> > BELEGUNGSEINHEIT_NR = 3430
> >
> > That's ARBEITSPLAN_NR with only 3 records with
BELEGUNGSEINHEIT_NR
> =
> > 3400,3500 or 3430 in any combination.
> >
> > I left my database query in German because I think that you might
> > understand it better Dieter.
>
>
> This should work for you:
>
> select ARBEITSPLAN_NR
> from w100.papp
> where papp.BELEGUNGSEINHEIT_NR in (3400,3500,3430)
> group by ARBEITSPLAN_NR
> having count (papp.ARBEITSPLAN_NR) = 3;
>
>
> David Fitzjarrell

If that query does not perform as expected try this:

select ARBEITSPLAN_NR
from w100.papp
where papp.BELEGUNGSEINHEIT_NR in (3400,3500,3430) and ARBEITSPLAN_NR in
(select papp.ARBEITSPLAN_NR
 from w100.papp
 group by ARBEITSPLAN_NR
 having count (papp.ARBEITSPLAN_NR) = 3);

David Fitzjarrell Received on Fri Feb 18 2005 - 08:48:52 CST

Original text of this message

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