not in the same table

Asked

Viewed 43 times

0

Let’s say I have the table CON_CONTRATOS and in it I have con_customerId, con_vencimento among others. And I need to make a select that returns ALL contracts of a Costomer whose same only contract with con_vencimento not null. That is, if he has a contract with con_expiration null, he returns nothing. I tried to do this using not in, but I did not succeed. I had tried this way :

SELECT con_customer
FROM con_contratos 
WHERE con_vencimento IS NOT NULL
  AND NOT EXISTS (SELECT * FROM con_contratos WHERE con_vencimento IS NULL);
sql pl-sql oracle12c

  • I didn’t understand your question. By only the WHERE con_vencimento IS NOT NULL doesn’t answer your case?

  • Pq, this would return the contracts that are not null, even if it has at least 1 null, in case I need it to return, only if none of the contracts of the same is null

1 answer

0


You need to check if the Customer has null contracts, the way you did is checking every table without specifying it:

SELECT con_customer
FROM con_contratos C1
WHERE NOT EXISTS (SELECT * 
                  FROM con_contratos C2 
                  WHERE C1.con_customerId = C2.con_customerId
                    AND C2.con_vencimento IS NULL);

Browser other questions tagged sql pl-sql oracle12c

You are not signed in. Login or sign up in order to post.