选择内选择工作不正常,并显示错误
问题描述:
我有2个表:选择内选择工作不正常,并显示错误
t_orderPayment with n_id,n_order,n_paytype AND
t_paytype with n_ID , str_desc
我的查询是:
SELECT t_OrderPayment.n_order ,
t_OrderPayment.n_paytype = (select t_paytype.str_desc as n_paytype from t_PayType, t_orderpayment where t_OrderPayment.n_PayType = t_PayType.n_ID)
FROM t_OrderPayment
WHERE (((t_OrderPayment.n_PaymentStatus)<>-7)) ;
它返回错误
返回“最多一个记录可以此子查询”
我试图用MAX和TOP 1像n_paytype =(选择最大(t_paytype.str_desc)设置,但表明了我错误Expr1001 Str.desc只有文本值,这可能是原因。可能是INNER JOIN可以帮助我吗?
答
看起来你不小心做了一个交叉连接。您不需要在子查询中第二次参考t_OrderPayment
表 - 尝试删除(但将where子句保留为将它链接到外部查询的那个子句)。它看起来像整个子查询可以去掉,变成一个连接,但...
SELECT t_OrderPayment.n_order,
t_paytype.str_desc AS n_paytype
FROM t_OrderPayment
INNER JOIN t_PayType
ON t_OrderPayment.n_PayType = t_PayType.n_ID
WHERE t_OrderPayment.n_PaymentStatus <> -7;
答
SELECT t_OrderPayment.n_order, t_paytype.str_desc AS n_paytype FROM t_OrderPayment,t_PayType WHERE t_OrderPayment.n_PayType = t_PayType.n_ID AND t_OrderPayment.n_PaymentStatus <> -7;
您可以格式化代码并指定期望的结果是什么? –
哪个[DBMS](https://en.wikipedia.org/wiki/DBMS)您使用的? –
如果此查询'从t_PayType中选择t_paytype.str_desc作为n_paytype,t_orderpayment其中t_OrderPayment.n_PayType = t_PayType.n_ID'返回多个标量值,则不能在分配子选择内使用它... – Shnugo