CODEWITHSUNDEEP
sql-server
akd
akd

Reputation: 6758

How to query join table "ambiguous 'multi-part identifier' could not be bound"

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?

Upvotes: 0

Views: 67

Answers (3)

Speuline
Speuline

Reputation: 201

You're missing a from and maybe you should think about putting a "IN" clause instead of "=". 

...
ug.GroupName IN (SELECT GroupName  
...

It'll prevent the query from crashing if there are severals users with the same name...

Upvotes: 0

Pooja-G
Pooja-G

Reputation: 518

Try this query,

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

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Your subquery is missing a FROM:

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

Upvotes: 4

Related Questions