Re: Select parents w/ only 1 child

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 Fri Jan 18 2002 - 11:32:55 CST