Getting “No column was specified for column 1" when joining a derived table

Question

I am writing a left join and getting a

No column was specified for column 1" error.

What am I doing wrong?

LEFT JOIN 
(
   SELECT  CASE WHEN COUNT(*) > 1 THEN '' ELSE mycolumn END
   FROM 
    (
        SELECT code_value 
        FROM allocation 
        WHERE allocation.id='[val]'
        GROUP BY code_value
    ) grp
   GROUP BY code_value
) AS code4table
ON code4table.itemid = table1.id

I want to return code_value value if all values are same else return '' I am guessing I also need to include the code4table.itemid in my select query but not sure how.

Thanks in advance for the help.

Answer 1

In addition to an alias for the derived table (ALIASTABLE), you need a column alias (ALIASCOLUMN below) for the computed column:

LEFT JOIN (
   SELECT  CASE WHEN COUNT(*) > 1 THEN '' ELSE mycolumn END AS ALIASCOLUMN
   FROM
   ...
) AS ALIASTABLE
ON ALIASTABLE.ALIASCOLUMN = outertable.column

In your case ALIASCOLUMN name should be itemid in order to match the outer join naming.

(Because you are deriving a new column with the case / when, even though you have a named column mycolumn in the ELSE, Sql won't default the derived column name to this). If it makes sense to do so, you can retain the same column name (mycolumn) and change the final ON to match.