Re: Trying to simplify an sql query

On 17 Apr 2006 18:07:03 -0700, "dn.usenet" <dn.usenet_at_gmail.com> wrote:

>
>I am using the following sql query which I feel could be simplified,
>but I don't know how. For t1, fields (project + year) form the Pr Key.
>
>select t1.location from t1 aa, t2 t2
> where aa.project = t2.project and aa.year = t2.year and aa.month =
>t2.month
> and exists
>(select 1 from t1 bb where aa.project = bb.project and aa.year =
>bb.year
>having count(*) = 1
>group by bb.project, bb.year, bb.month
>)
>
>I think I have tried the following construct for the subquery
>but it hanged the database; maybe I tried a query which is slightly
>different than the one I am quoting below, but I don't want
>to risk hanging the database again.
>
>(select 1 from null having count(*) = 1
>group by aa.project, aa.year, aa.month)
>
>Thanks in advance.

logically speaking the subquery should return 1 if any record exists in any month for the combination of project and year. As month is no part of the primary key and exists will invariably select only 1 record, you don't need the month and you don't need the count(*) and the group by.

Hth

--
Sybrand Bakker, Senior Oracle DBA