CODEWITHSUNDEEP
mysqlsql
Michi
Michi

Reputation: 5481

Compare two query results and check if value exist in both of them

Table (DB-Fiddle)

CREATE TABLE sales (
    id int auto_increment primary key,
    product VARCHAR(255),
    sales_date DATE,
    insert_time DATE
);

INSERT INTO sales
(product, sales_date, insert_time
)
VALUES 
("Product A", "2020-04-15", "2020-03-05"),
("Product B", "2020-05-20", "2020-03-05"),
("Product C", "2020-02-11", "2020-03-05"),

("Product A", "2020-04-15", "2020-03-06"),
("Product B", "2020-05-20", "2020-03-06"),
("Product C", "2020-02-11", "2020-03-06"),

("Product A", "2020-04-15", "2020-03-07"),
("Product B", "2020-05-20", "2020-03-07"),
("Product C", "2020-02-11", "2020-03-07");

In the table above each day the sales of products are inserted and assigned to the insert_time.

Now I want to check per insert_time if the products in the result of this query:

SELECT
product,
sales_date
FROM sales
WHERE insert_time = "2020-03-05";

also appear in the results of this query:

SELECT
product,
sales_date
FROM sales
WHERE insert_time = "2020-03-05"
AND sales_date BETWEEN insert_time AND DATE_ADD(insert_time, INTERVAL 3 MONTH)

The final result should look like this:

product           sales_date         Check
Product A         2020-04-15         O.K.
Product B         2020-05-20         O.K.
Product C         2020-02-11         Error  (--> Because it only appears in the first query)

How do I have to merge the two queries in order to achive the expected result?

Upvotes: 1

Views: 80

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271061

A CASE expression with EXISTS comes to mind:

SELECT s.product, s.sales_date,
       (CASE WHEN EXISTS (SELECT 1
                          FROM sales s2
                          WHERE s2.product = s.product AND
                                s2.sales_date BETWEEN s2.insert_time AND DATE_ADD(s2.insert_time, INTERVAL 3 MONTH)
                         )
             THEN 'OK' ELSE 'ERROR'
        END) as flag
FROM sales s
WHERE s.insert_time = '2020-03-05';

Note that check is a bad name for a column because it is a SQL keyword (used for check constraints).

Upvotes: 0

Jim Jimson
Jim Jimson

Reputation: 2528

You can solve this by using a CASE statement:

SELECT
product,
sales_date,
    CASE
    WHEN sales_date BETWEEN insert_time AND DATE_ADD(insert_time, INTERVAL 3 MONTH) THEN 'Ok'
    ELSE 'Error' END AS 'Check'
FROM sales
WHERE insert_time = "2020-03-05";

You could also use a LEFT JOIN, but this means you have to hit the table twice:

SELECT all_records.product, all_records.sales_date, ifnull(filtered_records.Check, 'Error') AS `Check`
FROM
(
SELECT
product,
sales_date,
'Ok' AS 'Check'
FROM sales
WHERE insert_time = "2020-03-05"
) all_records
LEFT JOIN
(
SELECT
product,
sales_date,
'OK' AS 'Check'
FROM sales
WHERE insert_time = "2020-03-05"
AND sales_date BETWEEN insert_time AND DATE_ADD(insert_time, INTERVAL 3 MONTH)
) filtered_records
 ON all_records.product = filtered_records.product
 AND all_records.sales_date = filtered_records.sales_date

Upvotes: 1

Related Questions