Re: Sql Query Help please

From: Chen, Yongbiao(³ÂÓ¾±ê) <ChenYB_at_unforgettable.com>
Date: 1997/10/31
Message-ID: <34593F53.5EA4BA15@unforgettable.com>#1/1

This is a multi-part message in MIME format.

--------------7EE91BE42C63A9298A68BDC8
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

You can try this query:
select s.stud_nbr
from students s,classes_attended c
where s.stud_nbr=c.stud_nbr
  and c.class_name in ('class1','class2',....) group by c.stud_nbr
having count(*)>=number_of_classes_specified_in_where_clause;

For example:
select s.stud_nbr
from students s,classes_attended c
where s.stud_nbr=c.stud_nbr
  and c.class_name in ('CLASS1','CLASS2','CLASS3') group by c.stud_nbr
having count(*)>=3;

I hope this query can help you.

Chen Yongbiao

TurkBear wrote:

> I have 2 tables, students and classes_attended in a 1 to many
> relationship.
>
> I need a query that will select students that have attended a specific
> set of classes and only if they have attended ALL the classes
> specified ( that's why a OR where clause dosen't work) - any ideas?
>
> stud_nbr is the key field in both tables.
>
> Thanks,
> John Greco
> Oracle DBA suffering from 'out-of-memory' error in brain.

--------------7EE91BE42C63A9298A68BDC8
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Yongbiao Chen
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             Yongbiao Chen
n:              Chen;Yongbiao
org:            Guangzhou Chia Tai Makro(Jiajing) Co., Ltd.
adr:            7/F, Dongshan Plaza;;No.69, Xianlie Road;Guangzhou;Guangdong;510095;P.R. China
email;internet: ChenYB_at_unforgettable.com
title:          Programmer
tel;work:       +86 20 87617188 ext 307
tel;fax:        +86 20 87628149

x-mozilla-cpt: ;0
x-mozilla-html: FALSE

version:        2.1
end:            vcard

--------------7EE91BE42C63A9298A68BDC8-- Received on Fri Oct 31 1997 - 00:00:00 CST