Re: Subquery Question

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 Received on Fri Feb 18 2005 - 08:30:23 CST