Home » SQL & PL/SQL » SQL & PL/SQL » Find results NOT in subquery
icon5.gif  Find results NOT in subquery [message #220384] Tue, 20 February 2007 08:36 Go to next message
abis123
Messages: 31
Registered: February 2007
Member
The layout of tables I have are in the form of a main table, an outcome table and a main_outcome table, main_outcome table being a link between the two. From the main table I am accessing a reference number, from the outcome table I am accessing an outcome.

I have created a SQL statement that displays all reference numbers that have a certain outcome. What I want to do now is display all reference numbers that do not have a certain outcome.

The problem I am finding with this is that although I can display all reference numbers that does match the criteria: "does not have the outcome 'Case closed'", it also includes reference numbers that do have an outcome of 'Case closed', as a reference can have multiple outcomes. This means that although a reference number can have 'Case closed', it can also have 'Suspended' etc and so displays all results.

What I want to get is a statement that would get all reference numbers which do not match reference numbers which have 'Case Closed'. This is a difficult one to explain, if you need any further information just give me a shout.

Thanks.
Re: Find results NOT in subquery [message #220387 is a reply to message #220384] Tue, 20 February 2007 08:53 Go to previous messageGo to next message
pmaupoil
Messages: 40
Registered: February 2007
Location: France
Member
Would definitely help if you could post your query.
icon1.gif  Re: Find results NOT in subquery [message #220390 is a reply to message #220384] Tue, 20 February 2007 09:00 Go to previous messageGo to next message
abis123
Messages: 31
Registered: February 2007
Member
SELECT DISTINCT M.REFERENCE, O.OUTCOME_NAME FROM LADO.MAIN M
LEFT OUTER JOIN LADO.MAIN_OUTCOME MO ON M.REFERENCE = MO.REFERENCE
LEFT OUTER JOIN LADO.OUTCOME O ON O.OUTCOME_ID = MO.OUTCOME_ID
WHERE O.OUTCOME_NAME = 'End Of Investigation'

This is the code for accessing all reference numbers that have an 'End Of Investigation' outcome.

The kind of this I was thinking of would be to:

SELECT M.REFERENCE FROM LADO.MAIN M WHERE REFERENCE NOT IN the above statement.
Re: Find results NOT in subquery [message #220391 is a reply to message #220384] Tue, 20 February 2007 09:03 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Try something like this:

 
create table test_case (case_number number, result varchar2(100))

SQL> insert into test_case values (1,'Case Open');

1 row inserted

SQL> insert into test_case values (1,'Case Investigated');

1 row inserted

SQL> insert into test_case values (1,'Case Closed');

1 row inserted

SQL> insert into test_case values (2,'Case Open');

1 row inserted

SQL> insert into test_case values (3,'Case open');

1 row inserted

SQL> insert into test_case values (4,'Case Closed');

1 row inserted

SQL> insert into test_case values (5,'Case Open');

1 row inserted

SQL> commit;


select case_number from test_case t where 'Case Closed' not in
select distinct result from test_case u where u.case_number = t.case_number)

CASE_NUMBER
-----------
          2
          3
          5

icon14.gif  Re: Find results NOT in subquery [message #220392 is a reply to message #220384] Tue, 20 February 2007 09:11 Go to previous message
abis123
Messages: 31
Registered: February 2007
Member
What a legend you are! Thanks very much!
Previous Topic: pagination (rownum) with the twist of reporting total count
Next Topic: Check Departmet Code
Goto Forum:
  


Current Time: Mon Dec 05 07:03:20 CST 2016

Total time taken to generate the page: 0.08499 seconds