Click here to Skip to main content
15,867,750 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi Friends,
Can anyone solve this query and help me??

SQL
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..
XML
select t.machineid from tbl_asset t where t.deliveryno=@docno 


when it returns more than 1 value facing error like

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


XML
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..??
Posted
Comments
Zoltán Zörgő 26-Jun-13 8:48am    
I suppose you could use IN instead of = (...on m.mcidno IN (select...), but using subquery in a join ON is quite unusual.
RelicV 26-Jun-13 9:01am    
Provide your table schema and the PK and FK relationship.!

I would change your query to this

SQL
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.
 
Share this answer
 
v2
Comments
VIP Venkatesan 26-Jun-13 11:12am    
Thank u..
Simon_Whale 26-Jun-13 11:14am    
your welcome :)
Try this query
SQL
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900