0

I'm having a bit of trouble wrapping my head around a way to make this JOIN work.

Postgres 11.5

I have two tables:

Table1:
customer_id
date
(various other fields)

Table2:
customer_id
date
(various other fields)

The tables are not the same, but the customer_id's and dates are in the same format so will be able to be joined.

I am trying to create a result set that contains both of the tables combined together (all columns) but with the following logic applied:

All data from Table1
Only records from Table 2 that DO NOT exist in Table 1
Joined on customer_id and date

My end goal is that I am trying to create a stacked area chart with this result set, but there are situations where a customer could be both in Table 1 and Table 2, so for a single given day, if someone exists who is like that, I don't want to double count that individual.

Appreciate the help!

1

Assuming all the columns are compatible:

SELECT * from table1
UNION ALL
SELECT table2.* from table2 left join table1 using (customer_id, date)
    where table1.customer_id is null

If not all the columns are compatible, you will have to do a bit of tedious manipulation of the select lists, rather than using *.

| improve this answer | |
  • This is pretty much exactly what I ended up going with but with just using "NOT EXISTS" instead. Thank you! – parchambeau May 14 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.