CODEWITHSUNDEEP
sqlamazon-redshift
scott martin
scott martin

Reputation: 1293

Redshift : Fetch value from table 1 if value exists else pull from table 2

I am using Redshift DB.

I have a list of customer IDs stored in a table customers

Query : select name from customers

Based on the purchase data I have a table that has the age of the customer. Table name : sales

Query : select name, age_group from sales

I have another table that has age_group of all customers irrespective of whether they exist in sales table. This table is called customer_profile

Query : select name, age_group from customer_profile

I am trying to build a query such that for every customer in customers I need to have a column labelled age_group.

Condition : If age_group value exists in sales then it needs to be pulled from sales else it needs to fetch the data from customer_profile table

Upvotes: 1

Views: 912

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Assuming you have no duplicates in sales or customer_profile, I would recommend:

SELECT c.*,
       COALESCE(s.age_group, cp.age_group) as age
FROM customers c LEFT JOIN
     sales s
     ON s.name = c.name LEFT JOIN
     customer_profile cp 
     ON cp.name = c.name AND
        s.name IS NULL;  -- no match in sales

This seems like the simplest solution, assuming you have no duplicates.

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32001

you can use union

select name, age_group from sales 
where age_group is not null or age_group<>0 
union 
select name, age_group from customer_profile
where age_group is not null or age_group<>0

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13026

how about this

SELECT
  t1.name,
  isnull(t1.cp_age, t1.s_age) as age_Group
FROM
  (
    SELECT
      c.name,
      c.age_group,
      cp.age_group as cp_age,
      s.age_group as s_age
    FROM
      customers c
      LEFT JOIN customer_profile cp on cp.age_group = c.age_group
      and c.name = cp.name
      LEFT JOIN sales s on s.age_group = c.age_group
      and c.name = s.name
  ) as t1

Upvotes: 1

Related Questions