Selecting any one from a relation

Question

I'm trying to write a SQL query to achieve the following (The actual schema in my problem is different but the idea is the same)

Say I have two tables

Employee table

------- ---- --- -------
empl_id name age dept_id
------- ---- --- -------
1       a    30  1
2       b    35  1
3       c    45  2
4       d    30  2
5       e    50  3
6       f    50  3

Department table

------- ---- 
dept_id name
------- ----
1       x
2       y
3       z

What I want to achieve is two fold

a) select the departments that have at least one employee whose age is less than 40 (say).
b) return the emp_id of any employee who belongs to that criteria in the same result set

So running the query on the above tables should return

dept_id emp_id
------- ------
1        1 <-- I don't care if emp_id returned is 1 or 2. Both satisfy the filter
2        4

I know I can achieve objective a) with this query

select
    dept_id
from
    Department d
where
    exists (
        select
            1
        from
            Employee e
        where
            e.age < 40
        and e.dept_id = d.dept_id
    )

But I can't think of a way to achieve b). Can somebody shed some light?

Thanks

Answer 1

This is even simpler, and I think is even faster than my previous answer, and the existapproach:

select dept_id, MIN(empl_id)
from Employee 
WHERE Employee.age < 40
GROUP BY dept_id

Answer 2

SELECT Department.dept_id, empl_id
FROM Department JOIN Employee ON Employee.dept_id = Department.dept_id
AND Employee.age < 40
GROUP BY Department.dept_id 

SQL Fiddle here.

If you use a RDBMS that requires an aggregate function to use GROUP BY, as, for example, MS SQL Server, you can do something like:

select Department.dept_id, MIN(empl_id)
from Department JOIN Employee ON Employee.dept_id = Department.dept_id
AND Employee.age < 40
GROUP BY Department.dept_id 

Answer 3

Try this statement.

select t.dept_id, min(t.empl_id) as empl_id

from
(
    select dept_id, empl_id
    from
    employee
    where
    age < 40
) t 

group by t.dept_id

Answer 4

try this,

;WITH T(DEPTID,EMPID,CNT_OF_EMP)
AS
(
SELECT D.DEPT_ID,E.EMPL_ID,COUNT(E.ID)
FROM DEPARTMENT D INNER JOIN EMPLOYEE E ON E.DEPT_ID = D.DEPT_ID AND E.age>40
GROUP BY E.EMPL_ID,D.DEPT_ID
)
SELECT EMPID FROM T