List records without reference in another table

Question

List records without reference in another table

Navigation

#1 by (4 votes)

1

I have three tables: - Customers - plans - services

In the services table I have servico_A, servico_B and servico_C that are listed in the plans table.

I need to list all clients that do not have a plan with service_C. Even though it has other plans with other services, it 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

mysql sql

asked by anonymous 24.11.2016 / 14:46

1 answer

Get JSON result with php Bootstrap 3 - Spacing between inputs

score 4 · Accepted Answer

You can use the EXISTS clause 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 should change to the real name.

EXISTS

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

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