'How do I do mulitple joins on two databases tables

The goal is to join all the same values (the duplicates) together. Email, timestamp and daystamp.

I have created one join statement

SELECT history.email, history.timestamp, payment.timestamp, 
       history.daystamp, payment.daystamp
FROM history
FULL OUTER JOIN payment ON history.email = payment.email
ORDER BY history.email;

I have all the unique email addresses. How do I do the same for the timestamp and daystamp?

Can I do three outer joins in one statement?

sqlpostgresql


Solution 1:[1]

Here are two methods that might be useful to adapt to your specific problem here although it's a little unclear without sample data.

Method 1:

SELECT  A.column2
        , B.column2
        , C.column2
FROM 
(
    (SELECT month, column2 FROM table1) A
    FULL OUTER JOIN
    (SELECT month, column2 FROM table2) B on A.month= B.month
    FULL OUTER JOIN 
    (SELECT month, column2 FROM table3) C on A.month= C.month
)

Method 2:

select
    A.column2,
    B.column2,
    C.column2
from (
    select distinct month from table1
    union
    select distinct month from table2
    union
    select distinct month from table3
) as X
    left outer join table1 as A on A.month = X.month
    left outer join table2 as B on B.month = X.month
    left outer join table3 as C on C.month = X.month

Solution 2:[2]

Something like this?

SELECT 
  case when p.payment_id is not null then 'p' else 'h' end as tbl
, coalesce(h.email, p.email) as email
, coalesce(h.timestamp, p.timestamp) as timestamp
, coalesce(h.daystamp, p.daystamp) as daystamp
FROM history h
FULL JOIN payment p
  ON h.email = p.email 
 AND h.timestamp = p.timestamp 
 AND h.daystamp is not distinct from p.daystamp 
WHERE (h.history_id is null or p.payment_id is null) 
ORDER BY coalesce(h.email, p.email);

tbl | email          | timestamp           | daystamp
:-- | :------------- | :------------------ | -------:
h   | [email protected] | 2022-02-22 22:22:22 | 20220222
p   | [email protected] | 2022-02-22 22:28:22 | 20220222
h   | [email protected] | 2022-02-22 22:22:23 | 20220222
p   | [email protected] | 2022-02-22 22:22:24 | 20220222

Test on db<>fiddle here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Johnny Saldana
Solution 2

Related Questions