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 I have a table with

User_ID, Resource_ID, Location_ID, INstance_ID
1,1,1,1
1,1,2,2
1,1,2,3
2,2,3,4
2,3,3,5
2,3,5,6
3,1,2,7
3,1,7,8
4,4,1,9
4,4,2,10
5,5,1,11
1,1,3,14
4,4,1,15
1,1,10,18
1,1,1,19
5,6,1,12
6,7,3,13
6,7,1,17


I need a list of instance_IDs where, for a specific User_ID and Resource_ID, that user/resource combination has a row in Location_ID 1 AND has rows where Location_ID is not 1.

For example:


Instance id 1 (as user 1 has resource 1 in location 1 and location 2)
Instance id 9 (as User 4 has resource 4 in locations 1 and location 2)


Expected output  (the 5 rows that have Location_ID = 1 and there is at least one other row with same User_ID and same Resource_ID but different than 1 Location_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
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