Select Statement Using Subqueries? 2

[kernal]

I need to find IDs that are taking courses where the course# is under 1000 and then they are also taking course#s greater or equal to 1000. Plus, they haven't taken courses in a term prior to 1066:

Example:

ID Term Course#
1 0986 1
1 1066 2
1 1078 1000

2 1068 99
2 1088 2002
2 1108 2555

3 1108 99

4 1118 1001

ID 2 would be included because they did not take a course prior to 1066 and has a course# under 1000 and course#s greater or equal to 1000.

ID 1 would NOT be included because they took a course prior to 1066
ID 3 would NOT be included because this ID only took a course under 1000.
ID 4 would NOT be included because this ID only took a course greater or equal to 1000

Help is appreciated!

 

[johnherman]

Untested. Note the use of alias for <table>


SELECT A.ID from <table> A where A.COURSE < 1000 and A.ID in
(
SELECT B.ID from <table> B where B.COURSE >= 1000 and B.ID in
(
SELECT C.ID from <table> C where C.TERM not < 1066
))

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[carp]

Also not tested:

(SELECT id FROM table_name WHERE course < 1000
 INTERSECT
 SELECT id FROM table_name WHERE course >= 1000)
MINUS
SELECT id FROM table_name WHERE term < 1066;

 

[johnherman]

carp's solution is probably better (faster, less resources) in an Oracle environment, as the Oracle "MINUS" is very efficient. My solution will work with any SQL database, however. What I'm implying is that some other databases do not recognize the "MINUS" command.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[kernal]

Thank you carp and johnherman for your replies. I have to work with this query tool and it doesn't have the option IN, INTERSECT or MINUS (dang!). It has condition types like EQUAL TO, NOT EQUAL TO, EXISTS, NOT EXISTS. Can a select statement with subqueries be created using these condition types?

I REALLY appreciate your help!

 

[johnherman]

Some of those query tools allow you to override the SQL that it generates with your own. I would look into that option. In other words, do a query against one table, then replace the SQL that the tool generates with what I provided (or what carp provided).

Alternatively, do you have access to Oracle SQL Developer? (ask your Oracle specialist).

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[carp]

OK, so you need vanilla SQL.
Let's try this (untested) approach:

SELECT a.id 
  FROM table_name a
       INNER JOIN table_name b
          ON a.id = b.id
 WHERE a.course < 1000
   AND b.course >= 1000
   AND NOT EXISTS (SELECT 'x' 
                    FROM table_name c
                   WHERE c.term < 1066
                     AND c.id = a.id);