INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Select Statement Using Subqueries?

Select Statement Using Subqueries?

(OP)
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!

RE: Select Statement Using Subqueries?


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


RE: Select Statement Using Subqueries?

Also not tested:

CODE

(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; 

RE: Select Statement Using Subqueries?

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


RE: Select Statement Using Subqueries?

(OP)
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!

RE: Select Statement Using Subqueries?

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


RE: Select Statement Using Subqueries?

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

CODE

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); 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close