Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Select parents w/ only 1 child
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