CODEWITHSUNDEEP
postgresql
yaodav
yaodav

Reputation: 1276

left join to default values table

I have a list of process that needs to run every day, ofter thy run the write to DB if the success or failed, its is the table:

CREATE TABLE
        process(name text, status text, reason text);

INSERT INTO
    process
VALUES
    ('run','OK',''),
    ('stop','ERROR','exception');

and I have another table that has the list of all the processes that need to run.

 CREATE TABLE
    full_process(name text, status text, reason text);


INSERT INTO 
    full_process 
VALUES
    ('run','ERROR','script didnt run'),
    ('stop','ERROR','script didnt run'),
    ('report','ERROR','script didnt run');

I want to left join them so il get all the process that runs from process table and the ones that didn't run to get the default value from the full_process table I tried this

SELECT
    a.*,
    b.*
FROM
    process as a
LEFT JOIN full_process as b ON a.name = b.name;

but I'm getting only the default value from the full_process table, and when I'm trying the opposite I'm getting only the ones from process table

Upvotes: 0

Views: 481

Answers (2)

yaodav
yaodav

Reputation: 1276

so the solution is this:

SELECT a.name, b.status,
b.reason 
FROM full_process as a
    LEFT JOIN process as b 
        ON a.name = b.name

and if I don't want to have null in the table

SELECT a.name,
    COALESCE(b.status, 'ERROR') AS statusת
    COALESCE(b.reason, 'RECORD MISSING') AS reason
FROM full_process as a
    LEFT JOIN process as b 
        ON a.name = b.name

Upvotes: 2

Bjarni Ragnarsson
Bjarni Ragnarsson

Reputation: 1781

You are probably looking for this:

SELECT
    a.*,
    b.*
FROM
    process as a
FULL OUTER JOIN full_process as b ON a.name = b.name;

Upvotes: 0

Related Questions