Problem with NOT EXISTS in SQL

Question

Problem with NOT EXISTS in SQL

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have a problem with a query in SQL. I have my table pedidos that has the fields numero pedido , fecha recepción and código cliente and I need to generate a list of the clients that have not placed orders in a certain period and it occurred to me to do so:

SELECT cod_cli
 FROM pedidos
 WHERE NOT EXISTS
           (SELECT cod_cli
             FROM pedidos
             WHERE fec_rep>20140809 AND fec_rep<20141010)

But when I give it to continue, I get this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT cod_cli
   FROM pedidos
   WHERE fec_rep>20140809 AND fec_rep<2' at line 3

What is the error?

sql mariadb

asked by sebastian 11.11.2016 в 02:35

source

2 answers

Nodejs Proxy coming out with connection from another computer 1 input and a select to 1 same variable

score 1 · Answer 1

Your query has 2 problems:

You will not find all customers who have not placed orders, because you rely on the same order table and you should base yourself on the customer table. (In the way you do, you would not find customers who have never placed an order and would list each customer as many times as orders have been placed.)

The syntax error is because the dates must be passed as parameters (which is recommended) or as literal constants (less recommended). If you decide to pass it as literal, the appropriate date format will depend on the database engine you are using, and sometimes the way it is configured.

Taking into account this and assuming that the field that relates the customer table and orders is cod_cli, and that your engine will recognize the date in ISO format (YYYYMMDD) the query should look something like this:

SELECT c.cod_cli
  FROM clientes c
 WHERE NOT EXISTS
           (SELECT 1
              FROM pedidos p
             WHERE p.cod_cli = c.cod_cli
               and p.fec_rep between '20140809' and '20141010')

score 0 · Answer 2

First, as the previous answer says, you should base yourself on the customer table.

Could you give us more information about your database? Is there a customer table?

Based on assumptions, I would do the following:

select c.cod_cli
FROM clientes c
left join (
    select cod_cli from pedidos
    where p.fec_rep>20140809 AND p.fec_rep<20141010) p
on c.cod_cli = p.cod_cli where p.cod_cli = null

In this way you select the clients of the 'clients' table that do not appear in the 'orders' table during the dates indicated.