Home » SQL & PL/SQL » SQL & PL/SQL » NOT EXISTS
NOT EXISTS [message #184927] Fri, 28 July 2006 14:41 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I'm having a problem where I have a list of clients who can have multiple staff assigned to each of them....

staff could be 1, 2, 3, or 4.

client A has staff 1 assigned
client B has staff 2, and 3 assigned
client C has staff 1, and 4 assigned
client D has staff 1, and 3 assigned
client E has staff 1, and 2 assigned
client F has staff 1, and 4 assigned
client G has staff 3 assigned

I want to query those clients who do not have a staff assignment of 1, so the results should be clients B & G.

I have tried this with a NOT EXISTS and a NOT IN and am getting results for client's C, D, E, and F even though they have staff 1 assigned to them. The results of staff_assign.staff for each of them are 4,3,2,and 4 respectively. I don't want them to show up because they have staff_assign.staff of 1. What am I doing wrong?

Thanks, Stan



SELECT UNIQUE
   client.client_id
FROM
  client,
  client_provider_types,
  staff_assign,
  staff_location  
WHERE
       client.client_num = staff_assign.client_num
   AND staff_facility.provider_type = provider_types.provider_type
   AND staff_assign.staff_num = staff_location.staff_num
   AND client.location_id  =  'N'
   AND trunc(ddt.todate(ddt.fromdate(client.Date_dt))) IS NULL 
   and client.dept = 'sport'
  
   AND NOT EXISTS
      (SELECT 
         NULL
      FROM
         client,
         ccdata.facility,
         cpi
      WHERE
             client.client_num = staff_assign.client_num
         AND staff_facility.provider_type = provider_types.provider_type
         AND staff_assign.staff_num = staff_location.staff_num
         AND client.location_id  =  'N'
         AND trunc(ddt.todate(ddt.fromdate(client.Date_dt))) IS NULL 
         AND client.dept = 'sport'
         AND staff_assign.staff = '1'
         AND client_provider_types.provider_type = '1' )

[Updated on: Fri, 28 July 2006 14:58]

Report message to a moderator

Re: NOT EXISTS [message #184945 is a reply to message #184927] Fri, 28 July 2006 17:33 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
There is no way to figure out why you are not getting the rows you expect unless you supply some sample data. Using NOT EXISTS will work, as you can see below.

SQL> SELECT *
  2  FROM test
  3  ORDER BY client, staff;

CLIENT      STAFF
------ ----------
a               1
a               2
b               2
b               3
c               1
c               5
c               7

7 rows selected

SQL> SELECT *
  2  FROM test t
  3  WHERE NOT EXISTS (SELECT *
  4                    FROM test
  5                    WHERE client = t.client
  6                      AND staff = 1);

CLIENT      STAFF
------ ----------
b               3
b               2
The problem with your query is that you have joins in the subquery. Those could be restricting the rows sufficiently to cause the row not to show up even though staff_assign.staff = '1'. You are also are not aliasing the tables so there is no way for Oracle to pass the client_id from the main query to the subquery. I wouldn't even begin to guess how Oracle is parsing it.

Once you get that worked out, there is a more efficient way to write the query which I evolve below.
SQL> SELECT t.*
  2        ,MAX(CASE staff WHEN 1 THEN 1 ELSE 0 END) OVER (PARTITION BY t.client) one_exists
  3  FROM test t;

CLIENT      STAFF ONE_EXISTS
------ ---------- ----------
a               1          1
a               2          1
b               2          0
b               3          0
c               1          1
c               5          1
c               7          1

7 rows selected

SQL> SELECT *
  2  FROM   (SELECT t.*
  3        ,MAX(CASE staff WHEN 1 THEN 1 ELSE 0 END) OVER (PARTITION BY t.client) one_exists
  4  FROM test t) tv
  5  WHERE one_exists = 0;

CLIENT      STAFF ONE_EXISTS
------ ---------- ----------
b               2          0
b               3          0
Re: NOT EXISTS [message #184982 is a reply to message #184927] Sat, 29 July 2006 09:13 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Sorry for my inexperiance...I've just started writing queries about two months ago.
I'll start by putting aliases in my quere.


Thanks for your help.
Re: NOT EXISTS [message #184988 is a reply to message #184927] Sat, 29 July 2006 11:00 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
SELECT UNIQUE
client.client_id
FROM
client,
client_provider_types,
staff_assign,
staff_location
=======================
client_provider_types,staff_assign,staff_location should NOT be in the FROM clause because they contribute nothing to the SELECT clause.
They should be subordinated into the WHERE clause using EXISTS or IN
Re: NOT EXISTS [message #185043 is a reply to message #184927] Sun, 30 July 2006 09:34 Go to previous messageGo to next message
Peter D.
Messages: 19
Registered: June 2006
Location: Warsaw, Poland
Junior Member
Table structure can be useful. Maybe something like this:

SELECT client
FROM client_table
WHERE client NOT IN (SELECT client
                     FROM client_table
                     WHERE staff = 1;


Peter D.
Re: NOT EXISTS [message #185063 is a reply to message #184927] Sun, 30 July 2006 17:50 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
A problem with the original query is that the EXISTS subquery has nothing to tie it back to the main query. Therefore if the subquery finds anything (i.e. if there are any clients at all in the 'sport' department who have staff assigned to them) then the main query must return no rows.

btw, UNIQUE is valid but DISTINCT is more commonly used in Oracle, if that matters at all.
Previous Topic: date to DAY
Next Topic: Why oracle doesn’t allow direct DDL inside the proc?
Goto Forum:
  


Current Time: Sun Dec 04 04:15:08 CST 2016

Total time taken to generate the page: 0.06341 seconds