Problem in consultation NOT IN

Question

Problem in consultation NOT IN

Navigation

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

0

I have a problem with a query, initially very easy.

I created a table control_de_stock where is the information of the stock of thousands of articles, each of them with its "id producto" . I created another table ordenes_de_venta where there is also "id producto" and that is responsible for recording each sale that has been made.

I want to be able to find out what products have never been sold. For this I made this query. Separately I see that in stock control there are many more IDs than in order_of_sales, but nevertheless when executing the query it shows 0 rows.

select distinct "id producto" from control_de_stock
    where "id producto" not in
        (select distinct "id producto" from ordenes_de_venta);

Apart from the solution I would like to know what I am doing wrong or an explanation of why this is happening to me.

Thank you.

postgresql

asked by Joshua 04.02.2018 в 13:05

source

2 answers

Insert JavaScript variable to a Form action. with pug / jade Error Only variables should be passed by reference when Inserting data by query parameterized with PHP and MySQL

score 2 · Answer 1

2

It's easier if you do

SELECT distinct cs.id_producto 
FROM control_de_stock cs
LEFT JOIN ordenes_de_venta USING (id_producto)
WHERE ordenes_de_venta.id_producto IS NULL

answered by 21.02.2018 в 00:09

score 0 · Answer 2

It seems that there was a null or empty value in order_of_sales and it did not allow the query to work.

I used the following code to make it work:

select distinct "id producto" from control_de_stock cs
    where cs."id producto" NOT IN 
        (select distinct "id producto" from ordenes_de_venta where "id producto" > 1) order by "id producto";