Re: SQL question
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