I have a column named endate(its values are dates) present in five tables, straddle0, straddle1, straddle2, straddle3 and straddle4. My assumption regarding the data is that, one table's endate values is not present in any of the other mentioned tables(can be repeated in the same table though). But to confirm, I want to list all the endate values that might be present in multiple tables (like 01-01-2017 is present in straddle0 and also in straddle4 or 02-02-2017 is present in straddle1 and also in straddle3 and straddle5).

What is the PostgreSQL query for the same?

CodePudding user response：

I would use UNION ALL and a GROUP BY/HAVING:

Schema (PostgreSQL v13)

CREATE TABLE t1 (
    enddate date
);

CREATE TABLE t2 (
    enddate date
);

CREATE TABLE t3 (
    enddate date
);

INSERT INTO t1
VALUES (CURRENT_DATE), (CURRENT_DATE 1);

INSERT INTO t2
VALUES (CURRENT_DATE), (CURRENT_DATE 2), (CURRENT_DATE 2);

INSERT INTO t3
VALUES (CURRENT_DATE 2), (CURRENT_DATE 3);

Query #1

WITH all_dates AS (
    SELECT 't1' AS table_name, enddate
    FROM t1
    UNION ALL
    SELECT 't2' AS table_name, enddate
    FROM t2
    UNION ALL
    SELECT 't3' AS table_name, enddate
    FROM t3
)
SELECT enddate, ARRAY_AGG(DISTINCT table_name) AS appears_in
FROM all_dates
GROUP BY 1
HAVING COUNT(DISTINCT table_name) > 1
ORDER BY 1;

View on DB Fiddle

CodePudding user response：

Not sure what format you want the result in. I made two scripts - a simple one and a more detailed one. Perhaps this is what you need

Here is dbfiddle

CodePudding user response：

with data(dt, t) as (
    select distinct endate, 0 from straddle0 union all
    select distinct endate, 1 from straddle1 union all
    select distinct endate, 2 from straddle2 union all
    select distinct endate, 3 from straddle3 union all
    select distinct endate, 4 from straddle4
)
select dt, min(t) as t from data group by dt having count(*) = 1;