CODEWITHSUNDEEP
sqlselectsubquerynot-exists
hugmax
hugmax

Reputation: 48

Select records not in subquery, returns nothing

I have a table with customers and another with documents, I want to get all customers that don't are in the subquery having document type 2

SELECT * from contactos 
WHERE NOT EXISTS 
  (SELECT * from contactos 
   JOIN documentos ON documentos.cifnif = contactos.cifnif 
   WHERE documentos.idramo = 2 
   AND documentos.estado = 'Vigente'
  )

There are 5000 customers and only 845 on the subquery

But I got an empty result.

Upvotes: 0

Views: 44

Answers (1)

Mureinik
Mureinik

Reputation: 312257

Your subquery selects from contactos again, with no correlation to the contactos from the outer query. In other words, it will return the same result for any row of the outer query, and since it always returns something, the outer query will return no rows.

To fix this, you need to remove contactos from the from clause of the inner query, and instead correlate it with the table from the outer query:

SELECT * from contactos 
WHERE NOT EXISTS 
  (SELECT * 
   FROM   documentos 
   WHERE  documentos.cifnif = contactos.cifnif AND
          documentos.idramo = 2 AND 
          documentos.estado = 'Vigente'
  )

Upvotes: 1

Related Questions