Re: Subquery Question

> 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 :-)