Subquery returned more than 1 record

Rewrite the code using below syntax

Syntax:

1. SELECT * 
2. FROM
   WHERE Id IN (multiple row query)

   Give conditions to subquery

   +1
   

   Onkar Sharma3y agoPosted Dec 24, 2022, 3:19 PM

   Hi Ramco,

   I totally agree with Anoop, you need to use the "IN" operator otherwise it will give you a "Subquery returned more than 1 record" error message.

   When the subquery returns more than 1 value, you must use the IN operator:

   Syntax:

   1. SELECT * 
   2. FROM
   WHERE Id IN (multiple row query)

   Query:

   1. SELECT DISTINCT T0.Code, T0.Name    
   2. FROM Item T0 
   3. WHERE T0.ItemCode IN (SELECT DISTINCT(Itemcode) FROM Item2)  

    

   To know more about SQL Server Subqueries, visit:

   • https://learn.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver16
   • https://www.sqlservertutorial.net/sql-server-basics/sql-server-subquery/

    

   Thanks!

   +5
   

   Uday Dodiya4y agoPosted Aug 31, 2022, 7:11 AM

   Your sub query return more than one value so please change your query as per following

   1. Select distinct T0.code,T0.Name From Item T0 where T0.ItemCode IN (Select distinct(Itemcode) from Item2)  

    

   +4
   

   Asma Khalid4y agoPosted May 23, 2022, 5:05 AM

   Use 'in' instead of '=' operator i.e.

    

   1. Select distinct T0.code,T0.Name  
   2. from Item T0   
   3. where T0.ItemCode IN (Select distinct(Itemcode) from Item2) 
      

    

   Thanks & Regards,

   Asma Khalid

    

   For More Visit: www.asmak9.com or www.bytezaar.com

   +6
   

   Nitin Sontakke4y agoPosted May 20, 2022, 5:19 PM

   And if you are 100% sure that you only need the first value returned by your select statement, consider adding top 1 and for additional clarity an order by clause to your query.

   +6
   

   Jignesh Kumar4y agoPosted May 20, 2022, 3:30 AM

   Hi Ramco,

    

   If you want records not match itemCode in table2 then you can use union which will give you disticnt.

   1. select code,Name from Tab0  
   2. union  
   3. select ItemCode as code,Name from Tab1  

    

   +6
   

   Sachin Singh4y agoPosted May 19, 2022, 4:56 PM

   As Anoop said, you can't use the '=' operator when your subquery is returning or may return more than one value.

   For multiple values, IN operator is used. 

   +6
   RR

   Ramco Ramco4y agoPosted May 19, 2022, 4:21 PM

   Hi ANoop

    

     Actually i want those code which have different in tbl2 of that ItemCode Unique.

   Thanks 

   +5
   

   Anoop Kumar Sharma4y agoPosted May 19, 2022, 4:02 PM

   Hi,

    

   I can see you just edited the question completely. Earlier you have a different question. 

    

   1. Select distinct T0.code,T0.Name    
   2. from Item T0 where T0.ItemCode in (Select distinct(Itemcode) from Item2)    

    You need to use IN operator else it will give you Subquery returned more than 1 record error message.

    

    

   I hope this will help you. 

   +5
   

   Anoop Kumar Sharma4y agoPosted May 19, 2022, 3:56 PM

   Hi, 

    

   I have created a SQL Fiddle as per your requirement. Please have a look on it.

   http://sqlfiddle.com/#!18/2c6b1/8

    

   1. Select im1.Code from ItemMaster1 im1  
   2. inner join ItemMaster2 im2 on im1.Code = im2.code  
   3. where im1.[Desc]<>im2.[Desc];  

   I hope this will help you. 

   +5