Re: SQL question

jalford_at_immcms1.redstone.army.mil (huckleberry) wrote:
> 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

The reason that the results are multiplied together is that you did not specify a join condition for the two tables (i.e. you should have a clause saying WHERE l1.something = l2.something). Since you didn't specify a join condition, then the result is an "outer join" of the two tables (i.e. every record in l1 is joined to every record in l2). This is a rather fundamental concept of SQL, so if it's new to you then maybe you need some additional training in order to use SQL effectively. Go find a course in Hawaii or someplace nice, and tell your boss that you absolutely have to attend. Received on Sat Jun 17 1995 - 00:00:00 CEST