Pass parameter to subquery

Question

I'm using SSRS/SSDT in Visual Studio 2015 and SQL Server 2014. There's a bug that's been present for > 8 years where you can't select multiple columns from different tables that have the same name. To get around this, I need to use a subquery. Every single answer I find rewrites the given query to remove the subquery, which would normally be great but is not applicable in this case. How do I pass a parameter to a subquery in SQL Server?

Column aliases do not work with this bug--Using AS returns an unknown column error on the "duplicate" columns even though it works with all others. The last two lines in the SELECT clause work because the values are being queried so the report can use them, but the remainder of the actual query doesn't use them.

Here's my current code (doesn't work because the subquery returns multiple rows).

SELECT t.[Description],
       t.RequestedCompletionDate,
       t.CommitDate,
       t.StatusId,
       t.PriorityId,
       p.ProjectNumber,
       s.Name AS StatusDescription,
       pr.Name AS PriorityDescription
FROM ProjectTask t
inner join Project p
    on p.Id = t.ProjectId
inner join Project_TaskStatus s
    on s.Id = t.StatusId
inner join Project_Priority pr
    on pr.Id = t.PriorityId
WHERE t.Type = 'ET'
AND t.StatusId NOT IN (4,7)
AND
(
    SELECT StatusId FROM Project
    -- WHERE ?
)
NOT IN (3, 4)
ORDER BY t.PriorityId,
         t.CommitDate,
         t.RequestedCompletionDate

This is the code with aliases as requested in the comments. It throws an error:

SELECT t.[Description],
       t.RequestedCompletionDate,
       t.CommitDate,
       t.StatusId AS TaskStatusId,
       t.PriorityId,
       p.ProjectNumber,
       p.StatusId AS ProjectStatusId,
       s.Name AS StatusDescription,
       pr.Name AS PriorityDescription
FROM ProjectTask t
inner join Project p
    on p.Id = t.ProjectId
inner join Project_TaskStatus s
    on s.Id = TaskStatusId
inner join Project_Priority pr
    on pr.Id = t.PriorityId
WHERE t.Type = 'ET'
AND TaskStatusId NOT IN (4,7)
AND ProjectStatusId NOT IN (3,4)
ORDER BY t.PriorityId,
         t.CommitDate,
         t.RequestedCompletionDate

-- Invalid column name 'TaskStatusId'.
-- Invalid column name 'TaskStatusId'.
-- Invalid column name 'TaskStatusId'.
-- Invalid column name 'ProjectStatusId'.
-- Invalid column name 'ProjectStatusId'.

The ideal code is below, but it throws the error An item with the same key has already been added, which is the error that SSRS/SSDT throws when trying to return multiple columns of the same name.

SELECT t.[Description],
       t.RequestedCompletionDate,
       t.CommitDate,
       t.StatusId,
       t.PriorityId,
       p.ProjectNumber,
       p.StatusId,
       s.Name AS StatusDescription,
       pr.Name AS PriorityDescription
FROM ProjectTask t
inner join Project p
    on p.Id = t.ProjectId
inner join Project_TaskStatus s
    on s.Id = t.StatusId
inner join Project_Priority pr
    on pr.Id = t.PriorityId
WHERE t.Type = 'ET'
AND t.StatusId NOT IN (4,7)
AND p.StatusId NOT IN (3,4)
ORDER BY t.PriorityId,
         t.CommitDate,
         t.RequestedCompletionDate

Answer 1

I feel very stupid for this, but apparently it works to just call p.Id in the subquery. It knows that I'm referencing the parent query's value even though it's in the subquery. Thank you everyone for your help.

SELECT t.[Description],
       t.RequestedCompletionDate,
       t.CommitDate,
       t.StatusId,
       t.PriorityId,
       p.ProjectNumber,
       s.Name AS StatusDescription,
       pr.Name AS PriorityDescription
FROM ProjectTask t
inner join Project p
    on p.Id = t.ProjectId
inner join Project_TaskStatus s
    on s.Id = t.StatusId
inner join Project_Priority pr
    on pr.Id = t.PriorityId
WHERE t.Type = 'ET'
AND t.StatusId NOT IN (4,7)
AND
(
    SELECT StatusId FROM Project WHERE Id = p.Id
)
NOT IN (3, 4)
ORDER BY t.PriorityId,
         t.CommitDate,
         t.RequestedCompletionDate