×
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.

Students Click Here

JOIN Table to self to filter records

JOIN Table to self to filter records

JOIN Table to self to filter records

(OP)
Hi there,

I have a table that does not seem to be as normalized as it could be.  In it, a particular record may show up several times, each time having a different entry in  one particular field.  For example:
ID     STATUS     OTHER
rec1   exists     data
rec1   removed    data
rec1   destroyed  data
rec2   exists     data
rec2   removed    data
rec3   conceived  data
rec3   exists     data
rec4   destroyed  data

There's a ton more info for each record, but these are the fields I'm worried about.

What I need to do is query this table such as to return only the data that:
1) contains the status "exists";
2) does NOT contain the status "removed" or "destroyed";
3) may contain the status "conceived"

I am able to do this through 3 seperate queries, including one which joins the table to itself (in MS Access).

QUERY1 ) Get all the records with "EXISTS":

SELECT table.ID, table.STATUS
FROM table
WHERE (table.STATUS)="EXISTS";

QUERY2) GET all of the records that contain statuses I don't want:

SELECT table.ID, table.STATUS
FROM table
WHERE (table.STATUS) IN ('removed','destroyed');

QUERY3) Using MS Access wizardry, join these 2 queries.  I can filter the results to get only the ones that have a null value in the STATUS field of the filtered results.  This implies that while they did have a status of 'EXISTS'.

This gives results like:
ID     STATUS   STATUS_1
rec1   EXISTS   DESTROYED
rec2   EXISTS   REMOVED
rec3   EXISTS   <null>

Since what I want is just the records where the STATUS is EXISTS, and no match for any of the values I want to filter out (ie QUERY2), I can simply filter on the existence of the null value, and get all of the records I want.  The SQL for all this is simply

SELECT QUERY1.ID, QUERY1.STATUS, QUERY2.STATUS
FROM QUERY1 LEFT JOIN QUERY2 ON QUERY1.ID = QUERY2.ID
WHERE (QUERY2.STATUS) Is Null;

All this brings me to my final questions:
Can someone help me merge these things into a single query?
For the example above, I have downloaded some data and am trying to get it to work in a local Access database.  Eventually, I need to work with the real data, and I can only use the iSQL+ Oracle interface, which (to my understanding) only allows me to paste in my SQL query.  There is too much data for me to download it all and partition it neatly using multiple queries.

This has been bugging me all day.  If anyone can help me turn this into a single query for either MS Access or Oracle, i would be much obliged.

Thanks folks,
Mike
 

RE: JOIN Table to self to filter records

(OP)
Oh my cow, was that ever fast.  Thanks so much!  I guess I need to review my nested queries...

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! Already a Member? Login

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