Re: SQL question

From: Karsten Weikop <karsten_at_pip.dknet.dk>
Date: 1995/06/20
Message-ID: <karsten.40.00162DAB_at_pip.dknet.dk>#1/1


In article <jalford.202.00454243_at_immcms1.redstone.army.mil> jalford_at_immcms1.redstone.army.mil (huckleberry) writes:
>From: jalford_at_immcms1.redstone.army.mil (huckleberry)
>Subject: SQL question
>Date: Fri, 16 Jun 1995 08:34:47 UNDEFINED
 

>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

Because the 2 queies are joined.

For each row in table l1, all the rows in table l2 are joined (fetched).

This is the reason why 47 * 106 = 4982

Example:

Table l1 contains:

select * from l1;
col1



1
2

Table l2 contains:

select * from l2;
col1



3
4

Table l1 contains:

select col1,col2 from l1, l2;
col1 col2
----- ----

1      3 
1      4
2      3 
2      4

or instead:

select count(col1), count(col2) from l1, l2; count(col1) count(col2)
----------- -----------
4 4

If you want the result of the totals number of rows i table l1 and l2 you need to use views



Karsten Weikop (Email: karsten_at_pip.dknet.dk) PBJ Consult A/S
Roholmsvej 10G
DK-2620 Albertslund
Denmark
Phone: + 45 43 62 74 00
Fax..: + 45 43 62 74 24
                           Received on Tue Jun 20 1995 - 00:00:00 CEST

Original text of this message