**UsersInGroup** table

```
UserName, GroupName

```

**UserReports** table

```
UserName, ReportName, IsShared

```

I would like to run a query to find out a user shared reports for other users in the same group.

```
SELECT ur.ReportName FROM UserReports ur 
 JOIN UsersInGroups ug ON ur.UserName = ug.UserName 
    WHERE ur.IsShared =1 AND 
    ug.GroupName =(SELECT GroupName UsersInGroups WHERE UsersInGroups.UserName='admin')

```

But this query says "ambiguous 'multi-part identifier' could not be bound?"

The solution I can think of is to add GroupName column into UserReports table to make the query simple. However this seems to be redundant column.

What is this query type called and how can I write the query to get the correct result?