need to find instances which have multiple rows with the same userID and ResourceID and have a location of 1 and also have other locations
select distinct
  InstancesinLocation1.user_id,
  InstancesinLocation1.resource_ID,
  InstancesinLocation1.location_ID,
  InstancesinLocation1.Instance_ID
from
  (
    select User_ID,resource_ID, location_ID, Instance_ID
    from usage
    where location_ID=1
  ) InstancesinLocation1
join
  (
    select User_ID,resource_ID, location_ID, Instance_ID
    from usage where location_ID&lt;&gt;1
  ) InstancesNotinLocation1
on
  (
    InstancesinLocation1.User_ID = InstancesNotinLocation1.User_ID
    and
    InstancesinLocation1.resource_ID = InstancesNotinLocation1.resource_ID
  )
;




User_ID  Resource_ID  location_ID  Instance_ID
-------  -----------  -----------  -----------
1        1            1            1
1        1            1            19
4        4            1            9
4        4            1            15
6        7            1            17