List records without reference in another table

Question

I have three tables: -customers -plans -services

On the table services have servico_A, servico_B and servico_C that are related in the table plans.

I need to list all customers who do not have a service plan. Even if he has other plans with other services, he should not be listed.

SELECT
p.codcliente
FROM
planos p
LEFT JOIN servicos se ON  p.codser = se.codser
LEFT JOIN clientes c ON p.codcliente = c.codcliente
WHERE
#ele nao tenha nenhum servico_C
ORDER BY p.codcliente

Answer 1

You can use the clause EXISTS as follows:

SELECT c.*
  FROM clientes c ON 
 WHERE NOT EXISTS(SELECT p.codcliente
                    FROM planos p
                         INNER JOIN servicos se ON  p.codser = se.codser
                   WHERE p.codcliente = c.codcliente
                     AND p.descricao = 'servico_C')
 ORDER BY p.codcliente

I took the liberty of deducing the name of the column with the description of the service, but you must change to the real name.

EXISTS

The condition EXISTS of MySQL is used in combination with a subquery and is considered to be a condition to be met if the subquery return at least one line.

In the case of query presented we use a subquery checking if there is any plan connected to the service servico_C And to the client code(codcliente) and use in EXISTS. This would return all customers who HAS plans with servico_C, so we use the NOT before the EXISTS, thus reversing the selection.