Re: SELECT question

From: Gerard H. Pille <ghp_at_infosoft.be>
Date: 1996/07/18
Message-ID: <4sm27d$g2n_at_news.Belgium.EU.net>#1/1

In article <4sf5rn$5si_at_ring01.cs.utsa.edu>, Amine Y. Tarhini (atarhini_at_ringer.cs.utsa.edu) says...
>
>I appreciate any help I get to this question. The environment is ORACLE
>REPORTS 2.5, but the question is strictly SQL.
>
>Suppose:
>
>table1 table2 table3
>------ ------- -------
>col1 col1 col2 col3 col1 col2
>----- ---- ----- ---- ---- ----
>A1 A1 B1 C1 A1 B1
> A1 B1
> A1 B2
> A1 B2
>if I do
> select sum(decode(table2.col3,'C1',1,0))
> from table2,table1
> where table2.col2 = 'B1'
> and table2.col1 = table1.col1
> and table1.col1 = 'A1'
>I get 1, correct
>
>and If I do
> select count(distinct table3.col2)
> from table3,table1
> where table3.col1 = table1.col1
> and table1.col1 = 'A1'
>I get 2, correct
>
>I have to have one query for the above, but If I do
 ... censored
>I hope I modeled the example correctly after the actual query. Thanks again
>in advance for any help.

The "sum(decode(" in your first example is a bit stretched, since   select count(*)
    from table2,table1

   where table2.col2  = 'B1'
     and table2.col1  = table1.col1
     and table1.col1  = 'A1' 
     and table2.col3  = 'C1'

would yield the same result.

About the result you get, I would say "Of course you do!". Since table 2 and 3 are not related (in your where-clause), the number of rows returned is the cartesian product of the two. I don't think SQL is the right tool to provide you with an answer, but since you're using ReportWriter, you might obtain it with child-queries, but this will deteriorate response-times.

Now, if you don't mind playing dirty, and I am correct in guessing you want to know the number of records in table 2 having 'C1' in col3, you might try the following. I can't try it myself, my old 386 isn't fit to have oracle installed.

  select table1.col1 first,

         count(distinct table2.rowid) second,
         count(distinct table3.col2) third
    from table1, table2, table3      
   where table2.col1 = table1.col1
     and table2.col2 = 'B1'
     and table3.col1 = table1.col1
     and table1.col1 = 'A1'     

   group by table1.col1

I'd like to know if it worked.

-- 
Kind reGards
     \ /   |
      X    |
     / \   s
     Gerard

Received on Thu Jul 18 1996 - 00:00:00 CEST