ID taking certain classes then any other class that were taken 1

[kernal]

I hope I can explain my problem. I'm using a query manager tool that I can't just write a SQL statement (I click on table fields, etc. and then it creates the statement). It also won't let me have "FROM" in an expression so hopefully, I don't need it. Some quick examples of records and what I need:

"class_id" Table

ID Term Class
1 Fall 2008 10-15
1 Spring 2009 2010-30
1 Spring 2009 410-1

2 Fall 2008 10-15
2 Summer 2008 1010-4

3 Summer 2008 1010-4
3 Fall 2008 5000-60
3 Spring 2009 370-90

4 Fall 2008 900-17

I need to find IDs that have taken classes: Fall 2008 10-15 and\or Summer 2008 1010-4 then also if they have taken any other classes in Spring 2009 so my results from this list would look like:

ID Term Class
1 Fall 2008 10-15
1 Spring 2009 2010-30
1 Spring 2009 410-1

2 Fall 2008 10-15
2 Summer 2008 1010-4

3 Summer 2008 1010-4
3 Spring 2009 370-90

Help is appreciated. Thank you

 

[Dagon]

All I can do is tell you how to do this with SQL. As to whether your front-end will allow it or not, I have no idea. But the only thing I've used is a subquery, which is a standard bit of SQL and if your front-end can't cope with that, I suggest it's time to start using something else.

drop table courses;

create table courses (id number, term varchar2(20), class varchar2(20));

insert into courses values (1,   'Fall 2008', '10-15');
insert into courses values (1,   'Spring 2009', '2010-30');
insert into courses values (1,   'Spring 2009', '410-1');

insert into courses values (2,   'Fall 2008', '10-15');
insert into courses values (2,   'Summer 2008', '1010-4');

insert into courses values (3,   'Summer 2008', '1010-4');
insert into courses values (3,   'Fall 2008', '5000-60');
insert into courses values (3,   'Spring 2009','370-90');

insert into courses values (4,   'Fall 2008', '900-17');

commit;

select * from courses c2
where ((c2.term = 'Fall 2008' and c2.class = '10-15') or
       (c2.term = 'Summer 2008' and c2.class = '1010-4') or
       (c2.term = 'Spring 2009'))
and exists
(select 1 from courses c1
where c2.id = c1.id
and  (c1.term = 'Fall 2008' and c1.class = '10-15') or
     (c1.term = 'Summer 2008' and c1.class = '1010-4'))
/

Retired (not by choice) Oracle contractor.

 

[kernal]

Thanks dagon. I'll see what happens and post another message when I'm done.

 

[kernal]

It worked GREAT. Thank you so much dagon.