CODEWITHSUNDEEP
postgresql
ibrewster
ibrewster

Reputation: 3622

Postgresql-Select one row from table where value in many table matches?

I have two tables, call them a and b, where a is related to b in a one-to-many relationship. I would like to select any rows from table a where any of the many related records in table b match a criteria. A basic join doesn't work, because that will return one result for each row in table b that matches - I just want one result for each row in table a with one or more related records matching.

For simplified example, say I have a table Departments and related table Employees, where each employee has one department, but each department obviously can have multiple employees. I want a query that will give me one row per department that has one or more employees matching a given criteria - say the departments that have one or more employees that have earned "employee of the month". How would I do this? Thanks.

Upvotes: 1

Views: 3961

Answers (3)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125344

select distinct on (d.id)
    d.name
from
    department d
    inner join
    employee e on d.id = e.department_id
where e.age between 60 and 65

How to order it by any column:

select *
from (
    select distinct on (d.id)
        d.*
    from
        department d
        inner join
        employee e on d.id = e.department_id
    where e.age between 60 and 65
) s
order by name

Upvotes: 2

joop
joop

Reputation: 4513

SELECT * FROM department d
WHERE EXISTS (
   SELECT * FROM employee e
   JOIN badges b ON b.person_id = e.person_id AND b.badge = 'EotM'
   WHERE e.dep_id = d.dep_id
   AND e.gender = 'F'
   );

Upvotes: 3

stonemetal
stonemetal

Reputation: 6208

Sounds like a job for a subquery. Something like: Select * from dept where id in (select deptID from Emp where wasEOTM = true); ought to do the job.

Upvotes: 1

Related Questions