postgresql JOIN with multiple conditions

Question

I have two postgres tables:

worker_details_verification (verification_id BIGSERIAL, worker_id BIGINT, 
state TEXT, proofs TEXT[])
worker_details(worker_id BIGINT, name TEXT)

Now I want to get

    `verification_id, worker_id, proofs FROM` the table 
    `worker_details_verification`  

restricting records `WHERE state = 'Initial'

Now in addition to the above three columns, I want the name column from the worker_details table too, where the worker_id can be used to query the worker's name.

I tried the following query, but it did not work.

SELECT a.verification_id, a.worker_id, a.state, a.proofs, b.Name FROM 
worker_details_verification a FULL OUTER JOIN worker_details b ON 
a.worker_id = b.worker_id AND a.state = 'Initial';

It returns records where even a.state is not 'Initial' and also some erroneous records where all name from worker_detail are returned with NULL for worker_details_verification columns.

Answer 1

It sounds to me that rather than a Full Outer Join, you'd want a Left/Right since you're looking for data from Worker_Details_Verification and then to filter that, while also grabbing Worker_Details where applicable.

I took this:

SELECT a.verification_id, a.worker_id, a.state, a.proofs, b.Name 
FROM worker_details_verification a 
     FULL OUTER JOIN worker_details b ON a.worker_id = b.worker_id AND a.state = 'Initial';

And made it into this:

SELECT a.verification_id, a.worker_id, a.state, a.proofs, b.Name 
FROM worker_details_verification a 
     LEFT OUTER JOIN worker_details b ON a.worker_id = b.worker_id 
WHERE a.state = 'Initial';