Re: SELECT question

>(Gerard H. Pille) writes:
> Amine Y. Tarhini (atarhini_at_ringer.cs.utsa.edu) says:
>>

 [lines deleted]
>
>
>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

 ^^^^^^^^^^^^^^^^^

    That was the reason I was not getting the answer expected. Another reader     also pointed this to me

>you with an answer, but since you're using ReportWriter, you might obtain it
>with child-queries, but this will deteriorate response-times.

    Correct. I have this already set up this way in REPORTS. But I was     looking for ways to improve performance, through using a single query.
>
>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.

  It worked as far as getting the correct results. But the performance   problem was not solved. I'm going to put the query of the larger table   in the parent query and for the smaller ones in the child-queries in   REPORTS.
>--
>Kind reGards
> \ / |
> X |
> / \ s
> Gerard
>

    Thanks for the help.
    Amine.. Received on Sat Jul 20 1996 - 00:00:00 CEST