I have this database

CREATE TABLE user_auth_custom (
  id serial,
  name VARCHAR ( 255 )
);

CREATE TABLE payment_action (
  id serial,
  user_auth_custom_id integer,
  message VARCHAR ( 255 )
);

CREATE TABLE work_action (
  id serial,
  user_auth_custom_id integer,
  message VARCHAR ( 255 )
);

INSERT INTO user_auth_custom VALUES
    (1, 'borris'),
    (2, 'jeremy'),
    (3, 'joe'),
    (4, 'barry');

INSERT INTO payment_action VALUES
    (1, 1, 'Paid $20'),
    (2, 1, 'Paid $340'),
    (3, 1, 'Paid $12'),
    (4, 2, 'Paid $120');
    
INSERT INTO work_action VALUES
    (1, 1, 'Submitted work abc'),
    (2, 1, 'did stuff'),
    (3, 1, 'more things'),
    (4, 1, 'loafing about'),
    (5, 1, 'dancing'),
    (6, 1, 'push ups'),
    (7, 1, 'monwalk'),
    (8, 2, 'balanced books'),
    (9, 2, 'read mind');

User 1 (borris) has 3 payment actions and 7 work actions.

I want to return 10 results in this format:

etc...

My idea was to left join both payment and work table to the customer record. I thought this would make 10 records with either work or payment fields being null. Then I could nullcheck/coalesce to get result. Something like this:

select u.id user_id, COALESCE (wa.message, pa.message, '') message,
       case
           when pa.id is not null then 'Payment'
           when wa.id is not null then 'Work'
       end action_type
from user_auth_custom u
    LEFT JOIN work_action wa ON u.id = wa.user_auth_custom_id
    LEFT JOIN payment_action pa ON u.id = pa.user_auth_custom_id
where (pa.id != null or wa.id != null)
and.id = 1

But it doesn't behave like I imagined it would.

What is the correct way to get the data in the format I require?

CodePudding user response：

If I understand correctly, you can try to use UNION ALL subquery instead of OUTER JOIN

SELECT t1.*
FROM user_auth_custom u
LEFT JOIN 
(
  SELECT user_auth_custom_id UserId,message,'Payment' ActionType
  FROM payment_action 
  UNION ALL
  SELECT user_auth_custom_id UserId,message,'Work'
  FROM work_action
) t1
ON u.id = t1.UserId
WHERE u.id = 1

But if you don't need to get columns or filter data from user_auth_custom, we can use UNION ALL subquery directly

SELECT t1.*
FROM 
(
  SELECT user_auth_custom_id UserId,message,'Payment' ActionType
  FROM payment_action 
  UNION ALL
  SELECT user_auth_custom_id UserId,message,'Work'
  FROM work_action
) t1
WHERE UserId = 1

sqlfiddle