Re: SQL question

In article <jalford.202.00454243_at_immcms1.redstone.army.mil> jalford_at_immcms1.redstone.army.mil (huckleberry) writes:
>Why does this query not give the expected results as when I break it into tow
>seperate queries ??
>
>Combined query:
>
> select count(l1.class) NUMBER_LOANS,
> count(l2.class) NUMBER_DELINQ
>from loan l1, loan l2
>where l1.loan_status='OPEN' and
> l2.loan_status='OPEN' and
> months_between(to_date(l2.EXPIRATION_DATE,'MON-YY'),sysdate) <= -2
>
> results:
>
>NUMBER_LOANS NUMBER_DELINQ
>---------------------------- -----------------------------
> 4982 4982
>
>-----------------------------------------------------------------
>individual queries:
>
> select count(class) NUMBER_LOANS
> from loan
> where loan_status='OPEN'
>
> results: NUMBER_LOANS = 106
>
> and
>
> select count(class) NUMBER_DELINQ
> from loan
> where loan_status='OPEN' and
> months_between(to_date(EXPIRATION_DATE,'MON-YY'),sysdate) <= -2
>
> results: NUMBER_DELINQ = 47
>
>For some reason unknown to me, the numbers the combined query returns is the
>result of the two numbers multiplied together. So, how do i get the right
>answer from one query or can I do it with one query ??
>
>Thanks in Advance !!
>
> - huckleberry

If you'll notice, 106 * 47 = 4982.
What you will need in your WHERE clause is L1.key_field = L2.key_field
ie. something to join them together so it doesn't "multiply" your records.

James Richard (JRICHARD_at_TUNS.CA)
"No Assembler Required!" Received on Tue Jun 27 1995 - 00:00:00 CEST