not in the same table

Question

not in the same table

Navigation

#1 by (0 votes)

0

Let's say I have the CON_CONTRATOS table and I have con_customerId , con_vencimento among others. And I need to do a select that returns me ALL the contracts of a customer whose contract is only with con_vencimento not null . That is, if it has a contract with null conformation, it returns nothing. I tried doing this using not in , but I did not succeed. I had tried it 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

asked by anonymous 05.10.2018 / 13:07

1 answer

Error uploading apk in google play console Problems with dplyr filter ()

score 0 · Accepted Answer

You need to check if the customer has nulls, so you're checking the entire table without specifying this:

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