SQL: SUB-QUERY in a JOIN statement returns 'Invalid Column Name' error

Question

I have the following query where I have the following data:

UPC	LOCATION
0123	Albany
0123	Birmingham
0123	Austin
0124	Albany
0124	Birmingham
0125	Albany

And I want the output to be:

UPC	LOCATION
0123	Albany, Birmingham, Austin
0124	Albany, Austin
0125	Albany

The problem I am running into is that I am pulling from two different databases - A and B; The UPC comes from A and the LOCATION comes from B

So I figured I needed to use a JOIN with a subquery containing a SELECT statement where A.FORMULA_ID = B.FORMULA_ID.

This is the query I have come up with:

SELECT 
    STRING_AGG(B.UPC, ', '), C.LOCATION
FROM
    [DBO].FSFORMULA B
JOIN
    (SELECT DISTINCT A.LOCATION
     FROM [DBO].LOCDETAIL A) AS C ON C.FORMULA_ID = B.FORMULA_ID

But I am getting an error:

Invalid Column Name 'FORMULA_ID'

It seems to be pointing at C.FORMULA_ID.

I can't figure out what's wrong so any help will be greatly appreciated!

Answer 1

SELECT 
    STRING_AGG(B.UPC, ', '), C.LOCATION
FROM
    [DBO].FSFORMULA B
JOIN
    (
       SELECT DISTINCT A.LOCATION, A.FORMULA_ID
       FROM [DBO].LOCDETAIL A
    ) AS C 
    ON C.FORMULA_ID = B.FORMULA_ID