CODEWITHSUNDEEP
sqloracle-database
Bhushan
Bhushan

Reputation: 6181

find missing record on tomorrow

I am using Oracle10g.

I want to find missing record of tomorrow from table. Following is the query I have tried so far.

select distinct P.PID, P.CITY from PROFILES P, TABLE2 T2
where T2.PID = P.PID
    and P.PID not in (select PID from TABLE2 where DELDATE=trunc(sysdate+1))
    order by P.CITY, P.PID

Following is the table structure(simplified):

PROFILES
    PID varchar2(10)
    PNAME varchar2(50)
    CITY varchar2(20)

TABLE2
    PID varchar2(10)
    DELDATE date
    QTY number

TABLE2 contains deliveries(delivered/expected) of customers, I want to get customers who doesn't have deliveries tomorrow.

Above query is giving me output as extected, i.e. all the PIDs who doesn't have record for tomorrow in TABLE2.

My question is that can this query be written more elegantly? Specifically without using not in.

Upvotes: 0

Views: 36

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

So you are only joining table2 in order to check a profile exists in this table?

select pid, city
from profiles
where pid in (select pid from table2)
and pid not in (select pid from table2 where deldate = trunc(sysdate) + 1);

As both conditions (pid must be in table2 and pid must not be in table2 with deldate tomorrow) refer to the same table, you can do this in one pass:

select pid, city
from profiles
where pid in 
(
  select pid 
  from table2
  group by pid
  having count(case when deldate = trunc(sysdate) + 1 then 1 end) = 0
);

Upvotes: 1

Multisync
Multisync

Reputation: 8797

select P.PID, P.CITY 
from PROFILES P join TABLE2 T2 on T2.PID = P.PID
group by P.PID, P.CITY 
having sum(case when T2.DELDATE=trunc(sysdate+1) then 1 else 0 end) = 0;

Upvotes: 1

Related Questions