Re: Select parents w/ only 1 child

From: Calvin Crumrine <Calvin_Crumrine_at_dced.state.ak.us>
Date: Tue, 22 Jan 2002 09:18:31 -0900
Message-ID: <3C4DACF7.1E3A261D@dced.state.ak.us>

Got it. Needed to join the table to itself restricting one side to those loans have only one collateral record, as you showed me, and restricting the other side to the type of collateral desired. Final result is:

select A.loan_id, B.collateral_type
from (select loan_id, count(*)

      from collateral_tbl
      group by loan_id
      having count(*) = 1) A,
      collateral_tbl B

where A.loan_id = B.loan_id
and B.collateral_type = :value

Thanks muchly.

Calvin Crumrine wrote:

> 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:18:31 CST