Subquery returned more than 1 record

Question

Hi Select distinct T0.code,T0.Name from Item T0 where T0.ItemCode = (Select distinct(Itemcode) from Item2) Thanks

Jignesh Kumar · Answer

Hi Ramco, If you want records not match itemCode in table2 then you can use union which will give you disticnt. select code,Name from Tab0 union select ItemCode as code,Name from Tab1

Sachin Singh · Answer

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.

Onkar Sharma · Answer

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: SELECT * FROM WHERE Id IN (multiple row query) Query: SELECT DISTINCT T0.Code, T0.Name FROM Item T0 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!

Asma Khalid · Answer

Use 'in' instead of '=' operator i.e. Select distinct T0.code,T0. Name from Item T0 where T0.ItemCode IN ( Select distinct (Itemcode) from Item2) Thanks & Regards, Asma Khalid For More Visit: www.asmak9.com or www.bytezaar.com

Nitin Sontakke · Answer

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.

Anoop Kumar Sharma · Answer

Hi, I can see you just edited the question completely. Earlier you have a different question. Select distinct T0.code,T0. Name 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.

Anoop Kumar Sharma · Answer

Hi, I have created a SQL Fiddle as per your requirement. Please have a look on it. http://sqlfiddle.com/#!18/2c6b1/8 Select im1.Code from ItemMaster1 im1 inner join ItemMaster2 im2 on im1.Code = im2.code where im1.[ Desc ] im2.[ Desc ]; I hope this will help you.

Uday Dodiya · Answer

Your sub query return more than one value so please change your query as per following Select distinct T0.code,T0. Name From Item T0 where T0.ItemCode IN ( Select distinct (Itemcode) from Item2)

Ramco Ramco · Answer

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

Anandu G Nath · Answer

Rewrite the code using below syntax Syntax: SELECT * FROM WHERE Id IN (multiple row query) Give conditions to subquery

Subquery returned more than 1 record

Answers (10)