CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
lalaland
lalaland

Reputation: 341

How to select values from two different tables when certain conditions are met

I have a table:

DROP TABLE TBL_A;

CREATE TABLE TBL_A
(
    number_id int, 
    country varchar(50), 
    status varchar(50), 
    number_of_days int,
    datetime date
);

INSERT INTO TBL_A
VALUES (121144, 'USA', 'CLICKED',2, '2021-10-09'),
       (121144, 'USA', 'CLAIMED',2, '2021-10-10'),
       (121144, 'USA', 'BOUGHT',2, '2021-10-11'),
       (121111, 'CAD', 'CLICKED',3, '2021-10-09'),
       (121111, 'CAD', 'CLAIMED',3, '2021-10-10'),
       (121111, 'CAD', 'BOUGHT',3, '2021-10-11'),
       (121133, 'AUS', 'CLICKED',5, '2021-10-09'),
       (121133, 'AUS', 'CLAIMED',5, '2021-10-10'),
       (121133, 'AUS', 'BOUGHT',5, '2021-10-11');

I have another table:

DROP TABLE TBL_B;

CREATE TABLE TBL_B
(
    number_id int, 
    country varchar(50), 
    status varchar(50), 
    number_of_days int,
    datetime date
);

INSERT INTO TBL_B
VALUES (121144, 'USA', 'CLICKED',6, '2021-10-20'),
       (121111, 'CAD', 'BOUGHT',10, '2021-10-21'),
       (121133, 'AUS', 'CLAIMED',5, '2021-10-02');

I want to select everything from TBL_A, but if the same number_id and status is found in TBL_B, I want to select the value in TBL_B ONLY if the datetime is higher.

Is there a way to do this? In the example above, only USA&CLICKED and CAD&BOUGHT should be 'updated' since their values in the second table have a greater datetime and the rest of the records should come from the first table.

This is what I have so far, but having trouble completing it:

select 
    number_id, 
    country, 
    status, 
    number_of_days,
    datetime date
from 
    TBL_A A
left join
    (select 
         number_id, 
         country, 
         status, 
         number_of_days,
         datetime date
     from
         TBL_B) on A.NUMBER_ID = B.NUMBER_ID 
                and a.STATUS = b.STATUS

Upvotes: 0

Views: 1918

Answers (1)

Andreas Hauser
Andreas Hauser

Reputation: 92

This query give you just the results, when ID, Status are in both tables and date in table "TBL_B" is greater than the equivalent date in table "TBL_A"

SELECT
 B.*    --everything from B according to WHERE restrict
FROM
 TBL_A as A,
 TBL_B as B 
WHERE
 A.number_id = B.number_id 
 AND A.status = B.status 
 AND A.datetime < B.datetime

When you also need the results from TBL_A, you can expand this query with sql set operators (e.g. MINUS & UNION).

https://en.wikipedia.org/wiki/Set_operations_(SQL)

(
SELECT
 A.*    
FROM
 TBL_A as A
MINUS  --everything from "A", which is not included in "B"
SELECT
 B.*
FROM
 TBL_A as A,
 TBL_B as B 
WHERE
 A.number_id = B.number_id 
 AND A.status = B.status 
 AND A.datetime < B.datetime 
)
UNION ALL  -- Add the "B" results
SELECT
 B.*    -- everything from "B" according to WHERE restrict
FROM
 TBL_A as A,
 TBL_B as B 
WHERE
 A.number_id = B.number_id 
 AND A.status = B.status 
 AND A.datetime < B.datetime 
;

Upvotes: 1

Related Questions