Re: Trying to simplify an sql query

Sybrand Bakker wrote:
>
> 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.
>

You are correct. Let me re-post my query with one change. Please note : I am specifying a new primary key this time.

I am using the following sql query which I feel could be simplified, but I don't know how. For t1, fields (project + year + quarter) 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)

I want to ignore the cases where project-year-month combo occurs more than once, as in : project = proj, year = 2006, month = march, quarter = Q1 (or) Q2.
{proj, 2006, Q1, march}
{proj, 2006, Q2, march}

Thanks in advance. Received on Tue Apr 18 2006 - 01:39:10 CDT