Re: Select parents w/ only 1 child

Thanks. This seems to work to list all loans having only 1 child, but as soon as I restrict it to children of a specific type I get *more* records than when I omit the restriction. I've tried adding 'WHERE collateral_type = value' to the 1st SELECT, the 2nd SELECT, and as a 3rd SELECT replacing the table. Nothing works. Any ideas?

"Daniel A. Morgan" wrote:

> Use an inline view. A SQL statement in the form:
>
> SELECT field1, field2
> FROM (
> SELECT field1, field2, count(*)
> FROM table
> GROUP BY field1, field2
> HAVING COUNT = 1);
>
> Daniel Morgan
>
> Calvin Crumrine wrote:
>
> > I have a loans database with 2 tables-loans and collateral, linked by
> > LOAN_ID. Collateral PK is concatenated, LOAN_ID, COLLATERAL_TYPE. Each
> > loan can have 0 or more pieces of collateral. The collateral can be
> > different types, e.g. real estate, vehicle, etc.
> >
> > For whatever reason management wants a report of all loans with only 1
> > piece of collateral, and then they only want it reported if the
> > collateral is of a specific type.
> >
> > I'm stuck. I can select the count of a specific type, but how do I get
> > the count of that type for each loan in the collateral table? I suppose
> > I could write a PL/SQL block to loop thru the table, but it seems like
> > there should be some way to do this in SQL using nested queries.
> >
> > Any ideas? TIA.
Received on Tue Jan 22 2002 - 12:04:16 CST