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