Re: SQL query may not be enough, what else should I try?

From: Vincenzo Scervo <scervo_at_praxis.it>
Date: Tue, 21 Mar 2000 10:22:01 -0800
Message-ID: <10pobrm$qrb$1@fe2.cs.interbusiness.it>

Hello,
I guess something about your schema. I supposed table B contains the current status (enrolled, passed) and the date od the association between students and classes.

select a.*, count(*)
from a,b
where not exists(select * from b, c

                 where b.fkc = c.pk
                   and b.fka = a.pk
                   and c.type = "humanities"
                   and b.status = "enrolled")

  and a.pk = b.fka
  and b.date IN this year -- just to simplify group by a.*
having count(*)<18

Hope that helps. I haven't tried it so let me know.

--
VIncenzo Scervo

---

Praxis Calcolo S.p.A.
Via Cavour 261
00184 Roma
Tel.: 06 4871469
Fax.: 06 4746753

---

Non si vede bene che con il cuore,
l'essenziale è invisibile agli occhi

Craig Orson Oakland (COO I) <dotacion.nospam_at_st-helena.zzn.com> wrote in message news:8b6aql$75a_at_gap.cco.caltech.edu...
> Environment: Windows NT Reports 2.5 Oracle 10.7 NCA, 7.3
>
> I'm creating a report. I've got three tables to extract data from
> (simplified this for clarity).
>
> Table A holds all the student names, Table B has holds the associations
> between Table A and Table C. Table C contains all classes offered.
>
> I want to learn which students (undergrads) are not currently enrolled in
a
> particular group of classes (e.g. Humanities), and have not accrued at
least
> 18 total units - over the entire time so far spent here. The second part
> I've got, but the first part of that has me stifled for a good solution.
>
> How would I do that? It's kind of like proving a negative. I would guess
I
> could perform several queries, maybe create a view or two, but it gets
more
> complicated because while I have used SQL for querying against conditions
> met, I have not for conditions that haven't been met.
>
> Does this make sense? Have I stated it clearly? I seem to be blocked for
a
> solution at the moment, but putting something in simple terms helps. I
> wasn't coming to you for the answer, just as a sounding board.
>
> Thanks for reverberating
>
>
> Craig
>
>
Received on Tue Mar 21 2000 - 12:22:01 CST