Postgresql – Create result set with all records from LEFT table, and from the RIGHT table only bring records that do no exist in left already

join;postgresqlquery

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!

Best Answer

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 *.

Related Solutions

Forcing Oracle to use hash join for a subquery

I'd try using a WITH clause in combination with the MATERIALIZE hint to force materialization of the subquery first. Something like this:

WITH x as (select /*+ MATERIALIZE */
           from [your subquery join])
SELECT *
FROM table0, x
WHERE table0.id =x.id

Postgresql – How to create function(table_name) that returns a set of the table

You cannot use a parameter name (_t) as return type.
Use polymorphic types instead:

CREATE OR REPLACE FUNCTION vsr_versioning_at_time(_t anyelement, _d timestamp)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
       SELECT DISTINCT ON (gid) * 
       FROM   %s
       WHERE  vrs_start_time <= $1
       ORDER  BY gid, vrs_start_time DESC'
     , pg_typeof(_t)
     )
   USING _d;
END
$func$  LANGUAGE plpgsql;

And use RETURN QUERY EXECUTE to actually return the results.
Call (important!):

SELECT * FROM vsr_versioning_at_time(NULL::mytable, '2014-04-18 12:00');

A lot more details in this related answer on SO (last chapter):
Refactor a PL/pgSQL function to return the output of various SELECT queries

Best Answer

Related Solutions

Forcing Oracle to use hash join for a subquery

Postgresql – How to create function(table_name) that returns a set of the table

Related Question