Question 113826 in PostgreSQL Question posted in
The official documentation can be found here.

postgresql left join but dont fetch if matching condition found

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search