The multi-part identifier "..." could not be bound

Question

I get error (The multi-part identifier "f.FormID" could not be bound.) running this query:

select f.FormID, f.Title, fv.UserName
    from Forms f join (
        SELECT        FormID
            FROM            Reports
            WHERE        (ReportID = @ReportID)
        UNION 
        SELECT        FormRelations.ForigenFormID
        FROM            FormRelations INNER JOIN
                                    Forms ON FormRelations.ForigenFormID = Forms.FormID
        WHERE        (FormRelations.PrimaryFormID =
                            (SELECT        FormID
                            FROM            Reports
                            WHERE        (ReportID = @ReportID)))
        ) ids
        on f.FormID = ids.FormID

    LEFT OUTER JOIN (select top 1 UserName, FormID from FormValues where FormID = f.FormID and UserName = @UserName) fv
        ON f.FormID = fv.FormID 

Please someone help me :(

@bluefeet:

I want such a result:

01304636-FABE-4A3E-9487-A14B012F9A61    item_1  1234567890
C0455E97-788A-4305-876A-A15000CFE928    item_2  1234567890
7719F37E-7021-4ABD-91ED-A15301830324    item_3  1234567890

Answer 1

If you need to use your alias inside of your subquery like that, you might want to look at using the APPLY operator:

select f.FormID, f.Title, fv.UserName
from Forms f 
join 
(
    SELECT        FormID
    FROM            Reports
    WHERE        (ReportID = @ReportID)
    UNION 
    SELECT        FormRelations.ForigenFormID
    FROM            FormRelations 
    INNER JOIN Forms 
        ON FormRelations.ForigenFormID = Forms.FormID
    WHERE        (FormRelations.PrimaryFormID = (SELECT        FormID
                                                 FROM            Reports
                                                 WHERE        (ReportID = @ReportID)))
) ids
    on f.FormID = ids.FormID
CROSS APPLY
(
    select top 1 UserName, FormID 
    from FormValues 
    where FormID = f.FormID 
        and UserName = @UserName
) fv

Or you can use row_number():

select f.FormID, f.Title, fv.UserName
from Forms f 
join 
(
    SELECT        FormID
    FROM            Reports
    WHERE        (ReportID = @ReportID)
    UNION 
    SELECT        FormRelations.ForigenFormID
    FROM            FormRelations 
    INNER JOIN Forms 
        ON FormRelations.ForigenFormID = Forms.FormID
    WHERE        (FormRelations.PrimaryFormID = (SELECT        FormID
                                                 FROM            Reports
                                                 WHERE        (ReportID = @ReportID)))
) ids
    on f.FormID = ids.FormID
LEFT JOIN
(
    select UserName, FormID,
        ROW_NUMBER() over(PARTITION by FormID, UserName order by FormID) rn  
    from FormValues 
    where UserName = @UserName
) fv
    on f.FormID = fv.FormID
    and fv.rn = 1