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.

select papp.ARBEITSPLAN_NR
from w100.papp
group by ARBEITSPLAN_NR
having

   sum(case when papp.BELEGUNGSEINHEIT_NR in (3400,3500,3430)

               then 1 end) = 3
and count(*) = 3

The sum counts only rows with 3400,3500,3430 (the Case replaces any other value with null) and the count counts all rows. If they're both 3 ...

If your DBMS doesn't support CASE use DECODE/IF.

> I left my database query in German because I think that you might
> understand it better Dieter.

Well, i understand the column names better, but that doesn't help that much :-)

Dieter Received on Fri Feb 18 2005 - 06:33:50 CST