在连接派生表时获取“没有为列1指定列”

I am writing a left join and getting a

我正在写一个左连接并得到一个

No column was specified for column 1" error.

没有为第1列“错误指定列。

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.

我希望返回code_value值,如果所有值都相同则返回''我猜我还需要在我的select查询中包含code4table.itemid但不确定如何。

Thanks in advance for the help.

先谢谢您的帮助。

1 个解决方案

#1

You need a column alias (ALIASCOLUMN below):

您需要一个列别名(下面的ALIASCOLUMN):

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.

在您的情况下,ALIASCOLUMN名称应为itemid以匹配外部联接命名。

(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.

(因为您正在使用case / when派生一个新列,即使在ELSE中有一个命名列mycolumn,Sql也不会将派生列名默认为this)。如果这样做有意义,您可以保留相同的列名(mycolumn)并将最终的ON更改为匹配。

#1