Re: Trying to simplify an sql query

sybrandb_at_yahoo.com wrote:
> Hopefully you are not playing games this time. You really should try to
> post the query you want help on right away.
> Note I don't comment on the table design.
>

Thanks. I wasn't playing games. The PK in the actual case consists of 8 fields, so I changed the query slightly to reduce the number of fields to enable the reader to understand it more quickly, and made a mistake while doing so the first time.

The table design should have had a pr key of 9 fields, but the client somehow prefers not to have the 9th field in the key.

My aim was to see whether the query could be written more efficiently by avoiding the use of 'from t1' twice, but your solution also uses it twice : 'from t1 aa' and 'from t1 bb'. The query is appended below.

Thanks, again.

>
> select t1.location from t1 aa, t2 t2
> where aa.project = t2.project and aa.year = t2.year and aa.month =
> t2.month
> and 1 =
> (select count(*) from t1 bb where aa.project = bb.project
> and aa.year = bb.year
> group by bb.project, bb.year
> )
>
Received on Tue Apr 18 2006 - 11:00:21 CDT