Question: This sounds like a very simple query but I have never needed this calculation before. I'm using SQL Management Studio and SQL Server 2008.

I have a table **ct\_workers** which contains individual employees and a second table cs\_facilities which shows the sites that they work at.

The table ct\_workers has a field **person** which is the primary ID for each employee and has a field **facility** which links the employees to **cs\_facilities** via a field **guid**

I'm looking to display all workers that have 2 or more facilities.
I've though about using Excel or rownumber but surely that must be a simple efficient way of doing this?

Can anyone assist please?

Thanks,

Answer: 
You can use a `GROUP BY` with `HAVING`

```
SELECT cw.person
FROM ct_workers cw
GROUP BY cw.person
HAVING COUNT(DISTINCT cw.facility) >= 2

```
Your question suggests that you can use aggregation:

```
select w.person
from ct_workers w
group by w.person
having min(w.facility) <> max(w.facility);  -- at least 2 values

```

However, if the `person` is the unique key in `ct_workers`, then a person can only be in one facility. So, your question would not make sense. You should actually have a junction table with one row per person and per facility.