Only display values that exist in two data-pools using EXIST statement

user_4934150 0
Viewed 23

DB-Fiddle

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    sales_date DATE,
    customer VARCHAR(255)
);

INSERT INTO sales
(sales_date, customer)
VALUES 
('2020-01-17', 'customer_01'),
('2020-01-19', 'customer_02'),
('2020-01-20', 'customer_02'),
('2020-01-22', 'customer_03'),

('2021-01-04', 'customer_05'),
('2021-01-11', 'customer_01'),
('2021-01-15', 'customer_03'),
('2021-01-22', 'customer_03'),
('2021-01-31', 'customer_04');

Expected Result:

sales_date  |    customer   | 
------------|---------------|-----
2021-01-11  |  customer_01  | 
2021-01-15  |  customer_03  |
2021-01-22  |  customer_03  |

I want to query all customers that appear in the period BETWEEN '2021-01-01' AND '2021-01-31' and also exist in the period BETWEEN '2020-01-01' AND '2020-01-31' therefore I tried to go with this query:

SELECT
t1.sales_date,
t1.customer
FROM 

  (SELECT
   sales_date,
   customer
   FROM sales
   WHERE sales_date BETWEEN '2021-01-01' AND '2021-01-31') t1

WHERE EXISTS

  (SELECT
   customer
   FROM sales
   WHERE sales_date BETWEEN '2020-01-01' AND '2020-01-31')
    
ORDER BY 2;

The result of this query includes the customer_04 and customer_05 although they do not appear BETWEEN '2020-01-01' AND '2020-01-31'.

Therefore, someowh the EXIST statement is compeletly ignored.
How do I need to modify it to make it work?

I know I could solve the specific case above by joining the the two tables instead of using EXIST.
However, in my original database I need to use EXIST so I am looking for a solution with the EXIST statement.

postgresql sql
2 Answers

The query should be dependent on the specific value from t1. Add the condition "AND s.customer = t1.customer".

SELECT
t1.sales_date,
t1.customer
FROM 

  (SELECT
   sales_date,
   customer
   FROM sales
   WHERE sales_date BETWEEN '2021-01-01' AND '2021-01-31') t1

WHERE EXISTS

  (SELECT
   customer 
   FROM sales s
   WHERE sales_date BETWEEN '2020-01-01' AND '2020-01-31'
     AND s.customer = t1.customer)
    
ORDER BY 2;
user_96797250

The EXISTS operator is a boolean operator that tests for existence of rows in a subquery.

someowh the EXIST statement is compeletly ignored

WHERE EXISTS

  (SELECT
   customer
   FROM sales
   WHERE sales_date BETWEEN '2020-01-01' AND '2020-01-31')

That why the above where condition become true and returned all the rows from sales_date BETWEEN '2021-01-01' AND '2021-01-31' in your code.

As joe suggested we must add dependant column to overcome the issue. Hers's my code

SELECT
   sales_date,
   customer
   FROM sales a
WHERE EXISTS
  (SELECT
   1
   FROM sales b
   WHERE a.customer = b.customer and sales_date  BETWEEN '2020-01-01' AND '2020-01-31'
   )
and  a.sales_date  BETWEEN '2021-01-01' AND '2021-01-31'   
ORDER BY 2;
user_159129840
Related