Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Select parents w/ only 1 child

Select parents w/ only 1 child

From: Calvin Crumrine <Calvin_Crumrine_at_dced.state.ak.us>
Date: Fri, 18 Jan 2002 13:41:54 -0900
Message-ID: <3C48A4B2.5822C0DD@dced.state.ak.us>


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 - 16:41:54 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US