Query to compare/merge two complex queries

Question

I have two queries:

Query #1: get all subscriptions

It returns which username has which subscription

SELECT 
    S.ScheduleID, C.[Path] ObjectPath, U.UserName, 
    SB.[Description], S.StartDate, S.LastRunTime
FROM 
    ReportServer.dbo.ReportSchedule RS
INNER JOIN 
    ReportServer.dbo.Schedule S ON S.ScheduleID = RS.ScheduleID
INNER JOIN 
    ReportServer.dbo.[Catalog] C ON C.ItemID = RS.ReportID
INNER JOIN 
    ReportServer.dbo.Subscriptions SB ON SB.SubscriptionID = RS.SubscriptionID
INNER JOIN 
    ReportServer.dbo.Users U ON U.UserID = SB.OwnerID

Results:

+:-----------:+----------+--------+-------------+---------+----------------+
| ScheduleID |ObjectPath|UserName| Description |StartDate| LastRunTime    |
+:-----------:----------:--------:------------+---------+----------------+
| 6D28791    |          | MPE\gaq|             |         |                |
| 6D28782    |          | MPE\Sam|             |         |                |
| 6D281w2    |          |        |             |         |                |
|            |          |        |             |         |                |
|            |          |        |             |         |                |
+------------+----------+------+-----+---------+----------------+---------+

Query #2: get inactive accounts.

This returns all the inactive users still in the server

SELECT 
    CAT.Name, U.UserName, ROL.RoleName, ROL.Description, U.AuthType
FROM 
    dbo.Users U
INNER JOIN 
    dbo.PolicyUserRole PUR ON U.UserID = PUR.UserID
INNER JOIN 
    dbo.Policies POLICY ON POLICY.PolicyID = PUR.PolicyID
INNER JOIN 
    dbo.Roles ROL ON ROL.RoleID = PUR.RoleID
INNER JOIN 
    dbo.Catalog CAT ON CAT.PolicyID = POLICY.PolicyID

Results:

+------------+----------+-------------+-----------+----------------+
| Name       |UserName  | RoleName    |Description|    Authtype    |
+------------+----------+-------------+-----------+----------------+
|Project X   |   MPE\asw|             |           |                |
|Project y   |   MPE\Sam|             |           |                |
|            |          |             |           |                |                
|            |          |             |           |                |                
|            |          |             |           |                |                
+------------+----------+-------------+-----------+----------------+

#Main Question: To find all the inactive users having subscriptions.

#I need to check if any values of the "UserName" column in table 2 exists in the "UserName" column of Table 1.

Does anyone know how to do this?

So far I tried joining the two columns but an error occurs every time.

SELECT 
    ScheduleID, UserName
FROM
    (SELECT 
         S.ScheduleID, C.[Path] ObjectPath, U.UserName, 
         SB.[Description], S.StartDate, S.LastRunTime
     FROM 
         ReportServer.dbo.ReportSchedule RS
     INNER JOIN 
         ReportServer.dbo.Schedule S ON S.ScheduleID = RS.ScheduleID
     INNER JOIN 
         ReportServer.dbo.[Catalog] C ON C.ItemID = RS.ReportID
     INNER JOIN 
         ReportServer.dbo.Subscriptions SB ON SB.SubscriptionID = RS.SubscriptionID
     INNER JOIN 
         ReportServer.dbo.Users U ON U.UserID = SB.OwnerID
) AS one
JOIN 
    (SELECT 
         CAT.Name, U.UserName, ROL.RoleName, ROL.Description, U.AuthType
     FROM 
         dbo.Users U
     INNER JOIN 
         dbo.PolicyUserRole PUR ON U.UserID = PUR.UserID
     INNER JOIN 
         dbo.Policies POLICY ON POLICY.PolicyID = PUR.PolicyID
     INNER JOIN 
         dbo.Roles ROL ON ROL.RoleID = PUR.RoleID
     INNER JOIN 
         dbo.Catalog CAT ON CAT.PolicyID = POLICY.PolicyID
) AS two ON one.ScheduleID = two.UserName

I get this error:

Level 16, State 1, Line 2
Ambiguous column name 'UserName'

*"so far I tried joining the two columns but error occurs everytime."* And what was that attempt? What was the error? — Thom A, May 17 '21 at 11:51
Also, what RDBMS are you *really* using? I *assume*, based on the `ReportServer` database and `dbo` schema that you are using SQL Server, *not* MySQL? — Thom A, May 17 '21 at 11:52
No, we're missing *Their*SQL @CaiusJard , along with TheirError. — Thom A, May 17 '21 at 11:55
Hey, that's right I am using a Microsoft SQL server. Sorry for the wrong Tag. I have updated the questions with my attempt and error. I am not able to join them properly.... — kum42, May 17 '21 at 12:19
Does this answer your question? [Query error with ambiguous column name in SQL](https://stackoverflow.com/questions/12662954/query-error-with-ambiguous-column-name-in-sql) — Luuk, May 17 '21 at 12:45
or, in words, `Ambiguous column name 'UserName'` means that SQL need to know if it has to display `one.UserName` or `two.UserName`, BTW, the on-clause (currently: `ON one.ScheduleID = two.UserName`) should that not be `ON one.UserName = two.UserName` ? — Luuk, May 17 '21 at 12:49
@Luuk Yes, I did use ON one.UserName = two.UserName but it showed the same error, it is refusing to recognize "UserName"...so I tried joining using different columns but still the same result.....Also, thanks for the link You sent ...I will try assigning aliases to the Parent select query — kum42, May 17 '21 at 13:04

d0little · Accepted Answer · 2021-05-17T13:37:48.037

You need to use correct aliases in the first select statement:

SELECT 
    one.ScheduleID, one.UserName
FROM
    (SELECT 
         S.ScheduleID, C.[Path] ObjectPath, U.UserName, 
         SB.[Description], S.StartDate, S.LastRunTime
     FROM 
         ReportServer.dbo.ReportSchedule RS
     INNER JOIN 
         ReportServer.dbo.Schedule S ON S.ScheduleID = RS.ScheduleID
     INNER JOIN 
         ReportServer.dbo.[Catalog] C ON C.ItemID = RS.ReportID
     INNER JOIN 
         ReportServer.dbo.Subscriptions SB ON SB.SubscriptionID = RS.SubscriptionID
     INNER JOIN 
         ReportServer.dbo.Users U ON U.UserID = SB.OwnerID) AS one
WHERE one.UserName IN 
    (SELECT 
         U.UserName
     FROM 
         dbo.Users U
     INNER JOIN 
         dbo.PolicyUserRole PUR ON U.UserID = PUR.UserID
     INNER JOIN 
         dbo.Policies POLICY ON POLICY.PolicyID = PUR.PolicyID
     INNER JOIN 
         dbo.Roles ROL ON ROL.RoleID = PUR.RoleID
     INNER JOIN 
         dbo.Catalog CAT ON CAT.PolicyID = POLICY.PolicyID)

Thanks, I just figured that out.. Also, I need to check if any values of the "UserName" column in table 2 exists in the "UserName" column of Table 1. So I don't think Join would itself be enough. Please let me know if there is a way to compare the two columns. Thanks! — kum42, May 17 '21 at 13:32

Query to compare/merge two complex queries

1 Answers1