Re: Complicated SQL Query... help?

Hi.

 Try using NOT EXISTS clause instead of MINUS.  Why do you need GROUP ( you don't have any aggregates ).  Isn't ORDER BY is good enough?

 HTH. Michael.

In article <7unkfg$d1g$1_at_nnrp1.deja.com>,   dolans_at_my-deja.com wrote:
> I have 2 queries where I get the Subjects that someone is currently
> enrolled in and another where the query retrieves ALL the subjects. I
> am trying to present a list to the user of those classes they haven't
> signed up for yet:
>
> I could do a MINUS operation to remove those classes they are already
> in, but I need to do a group by as well. Here is the query, maybe you
> can help. Thanks, Sean
>
> Query :
> SELECT ALL s.PERIOD, s.SUBJECT_DESCR,
> po.ORDINALITY, s.SUBJECT_ID
> FROM CTB.SUBJECT s, CTB.PERIODS_OFFERED po
> WHERE s.CONTRACT_ID=1
> AND (s.PERIOD=po.PERIOD
> AND s.CONTRACT_ID=po.CONTRACT_ID)
> MINUS
> (SELECT ALL s.PERIOD, s.SUBJECT_DESCR, po.Ordinality, s.SUBJECT_ID
> FROM CTB.SUBJECT s, CTB.USER_SUBJECT_ACCESS usa, CTB.PERIODS_OFFERED
po
> WHERE usa.USERNAME='foo'
> AND (usa.SUBJECT_ID=s.SUBJECT_ID)
> AND (po.period = s.period)
> )
> GROUP BY s.PERIOD, s.SUBJECT_DESCR,
> po.ORDINALITY, s.SUBJECT_ID
> ORDER BY po.ORDINALITY ASC
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 21 1999 - 18:06:40 CDT