0

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Will Buffington
  • 1,048
  • 11
  • 13
  • You are not selecting the column `FORMULA_ID` in your sub query. You need to select the column otherwise you cannot join on it. – Sebastian S. Feb 22 '23 at 19:20
  • Your subquery aliased as `C` only seems to have a `Location` column - not a `Formula_ID` column. If you want to join on `Formula_ID`, you need to also select that column inside your subquery ... – marc_s Feb 22 '23 at 19:21

1 Answers1

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
Sebastian S.
  • 1,173
  • 3
  • 13
  • 22