**我的加入查询中的子查询出错。 [英] ** Error at subquery in my Join Query.

问题描述

嗨朋友们，

任何人都可以解决这个问题并帮助我吗？

  create   proc  Sp_DespatchMachine 
  @ docno  < span class =code-keyword> int  
  as  
  select   distinct  m.mcidno，m.mcrefno，t.buyercomp，t.buyerfact 
  from  Tbl_Machine m  join  Tbl_Asset t 
  on  m.mcidno =（选择 t.machineid 来自 Tbl_Asset t 其中​​ t.deliveryno=@docno ）
  WHERE  t.deliveryno =  @ docno  

当我的子查询返回单个值时，它工作正常..

从tbl_asset t中选择t.machineid，其中t.deliveryno = @ docno 

当它返回多于1个值时面临错误，例如

子查询返回的值超过1。当子查询遵循=，！=，<  ，   <   =  ，  > ，> =或当子查询用作表达式时。 
该声明已被终止。

如果我的deliveryno = 1且我的机器= machin1上面的连接查询将起作用。 
 
如果我的deliveryno = 1且我的machineid =machine1，machine2。它将无效。

但是我想让它工作..有什么办法吗？



我们可以在子查询中使用循环吗？还是另一种简单的方法.. ??

解决方案

我会将您的查询更改为此

  CREATE   PROC  DespatchMachine 
  @ docno  < span class =code-keyword> INT  
  AS  
  SELECT   DISTINCT  m.mcidno，m.mcrefno，t.buyercomp，t.buyerfact 
  FROM  Tbl_Machine m  INNER   JOIN  Tbl_Asset t 
  ON  m.mcidno = t.machineid 
  WHERE  t .deliveryno =  @ docno  
 

p.s. 内部加入只会返回匹配。

尝试此查询

 创建  proc  Sp_DespatchMachine 
  @ docno   int  
  as  
 选择  distinct  m.mcidno，m.mcrefno，t.buyercomp，t.buyerfact 
 来自 Tbl_Machine m  join  Tbl_Asset t 
  on  m.mcidno =（选择  top   1  t.machineid  from  Tbl_Asset t  where  t.deliveryno=@docno)
  WHERE  t.deliveryno =  @ docno  

Hi Friends,
Can anyone solve this query and help me??

create proc Sp_DespatchMachine
@docno int
as
select distinct m.mcidno,m.mcrefno,t.buyercomp,t.buyerfact
from Tbl_Machine m join Tbl_Asset t 
on m.mcidno = (select t.machineid from Tbl_Asset t where t.deliveryno=@docno) 
WHERE t.deliveryno= @docno

when my subquery returns single value it works fine..

select t.machineid from tbl_asset t where t.deliveryno=@docno 

when it returns more than 1 value facing error like

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

if my deliveryno =1 and my machineid ="machin1" the above join query will work.

if my deliveryno =1 and my machineid = "machine1',"machine2". it will not work.

but i want to make it work.. is there any way?

can we use for loop in my subquery? or anyother easy way..??

解决方案

I would change your query to this

CREATE PROC DespatchMachine
  @docno INT
AS
 SELECT DISTINCT m.mcidno,m.mcrefno,t.buyercomp,t.buyerfact
 FROM Tbl_Machine m INNER JOIN Tbl_Asset t
 ON m.mcidno = t.machineid
 WHERE t.deliveryno= @docno

p.s. Inner Join will only return the matches.

Try this query

create proc Sp_DespatchMachine
@docno int
as
select distinct m.mcidno,m.mcrefno,t.buyercomp,t.buyerfact
from Tbl_Machine m join Tbl_Asset t
on m.mcidno = (select top 1 t.machineid  from Tbl_Asset t where t.deliveryno=@docno)
WHERE t.deliveryno= @docno

这篇关于**我的加入查询中的子查询出错。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！