postgresql left join but dont fetch if matching condition found-CodePudding

I have a bit of a complicated scenario. I have two tables, employee and agency. An employee may or may not have an agency, but if an employee has an agency I want the select clause to check another condition on the agency, but if the employee does not have an agency its fine I want to fetch the employee. I'm not sure how to write the select statement for this. This is what I have come up with so far

select * from employee e left join
agency a on a.id = e.agencyID and a.valid = true;

However the problem with this is that it fetches both employees without agencies which is fine, but it also fetches employees with agencies where a.valid = false. The only option I can think of is to do an union but I'm looking for something more simpler.

CodePudding user response：

A UNION could actually be the solution that performs best, but you can write the query without UNION like this:

select *
from employee e
   left join agency a
      on a.id = e.agencyID
where coalesce(a.valid, true);

That will accept agencies where valid IS NULL, that is, result rows where the agency part was substituted with NULLs by the outer join.

CodePudding user response：

You want except the condition that both table match(agency.id = employee.agencyID) and also agency.id is false. The following query will express the condition.

SELECT
    e.*,
    a.*
FROM
    employee e
    LEFT JOIN agency a ON a.id = e.agencyID
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            agency
        WHERE
            a.id = e.agencyID
            AND a.valid IS FALSE)
ORDER BY
    e.id;