Query to compare/merge two complex queries

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    |          | MPEgaq|             |         |                |
| 6D28782    |          | MPESam|             |         |                |
| 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   |   MPEasw|             |           |                |
|Project y   |   MPESam|             |           |                |
|            |          |             |           |                |                
|            |          |             |           |                |                
|            |          |             |           |                |                
+------------+----------+-------------+-----------+----------------+

#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’

Answer

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)

Advertisement

Answer