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

Question

4.00/5 (1 vote)

See more:

I have written the below query statement to sql server:

SQL

SELECT  ReferringPhysician.LastName,ReferringPhysician.FirstName,
    (SELECT LocationName FROM RefPhysLocations WHERE ReferringPhysicianID = (SELECT ReferringPhysicianID FROM RefPhysLocations WHERE LocationName LIKE '%'+@keyword+'%'))   AS LocationName
    FROM    ReferringPhysician
    WHERE   ReferringPhysician.ReferringPhysicianID=(SELECT ReferringPhysicianID FROM RefPhysLocations WHERE LocationName LIKE '%'+@keyword+'%')

But, I got the following error message from sql server.

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

Posted 18-Oct-10 18:43pm

velmahesh

Updated 18-Nov-20 22:01pm

Dalek Dave

v2

Add a Solution

Comments

Dalek Dave 19-Oct-10 3:42am

Minor Edit for Grammar.

3 solutions

Solution 2

hi Imdadhusen,

i tried based on ur statement... again came the same error msg.

XML

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

SQL

SELECT  ReferringPhysician.LastName,ReferringPhysician.FirstName,(SELECT TOP 1 LocationName FROM RefPhysLocations WHERE ReferringPhysicianID = (SELECT TOP 1 ReferringPhysicianID FROM RefPhysLocations WHERE LocationName LIKE '%'+@keyword+'%'))   AS LocationName
FROM    ReferringPhysician
WHERE   ReferringPhysician.ReferringPhysicianID=(SELECT ReferringPhysicianID FROM RefPhysLocations WHERE LocationName LIKE '%'+@keyword+'%'

Posted 18-Oct-10 19:12pm

velmahesh

Comments

Sunasara Imdadhusen 19-Oct-10 2:20am

why you couldn't use INNER JOIN instead of sub query

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sunasara Imdadhusen · Accepted Answer · 2010-10-18T18:52:00

Hi,

Please use TOP 1 in sub query like

SQL

SELECT  ReferringPhysician.LastName,ReferringPhysician.FirstName,(SELECT TOP 1 LocationName FROM RefPhysLocations WHERE ReferringPhysicianID = (SELECT TOP 1 ReferringPhysicianID FROM RefPhysLocations WHERE LocationName LIKE '%'+@keyword+'%'))   AS LocationName
FROM    ReferringPhysician
WHERE   ReferringPhysician.ReferringPhysicianID=(SELECT ReferringPhysicianID FROM RefPhysLocations WHERE LocationName LIKE '%'+@keyword+'%'

)

Please do let me know, if you have any doubt.

Please provide "Vote" if this would be helpful, and make "Accept Answer" if this would be correct answer.:rose:

Thanks,
Imdadhusen

velmahesh · Accepted Answer · 2010-10-18T21:38:00

Solution 3

finally i solved the problem...based on below..

SQL

SELECT ReferringPhysician.LastName  ,ReferringPhysician.FirstName,RefPhysLocations.LocationName
    FROM    ReferringPhysician LEFT JOIN RefPhysLocations
            ON ReferringPhysician.ReferringPhysicianID = RefPhysLocations.ReferringPhysicianID
    WHERE   LocationName LIKE '%'+@keyword+'%'

Posted 18-Oct-10 21:38pm

velmahesh

Comments

tasneemiram 8-Feb-13 0:11am

SELECT Couses_Name,
(SELECT B.Kilometers_Cancelled
FROM DOS_TripsperKms_Cancelled AS B INNER JOIN
Daily_Operational_Statistics AS C ON B.DOS_Id = C.DOS_Id
WHERE (C.Depot_Id = @id) AND (C.ST1_Date > @fromdate) AND (C.ST1_Date <= @todate) AND (A.Couses_Name = B.Reason_for_Cancellation))
AS Kilometers_Cancelled,
(SELECT B.Scheduled_Duty_No
FROM DOS_TripsperKms_Cancelled AS B INNER JOIN
Daily_Operational_Statistics AS C ON B.DOS_Id = C.DOS_Id
WHERE (C.Depot_Id = @id) AND (C.ST1_Date > @fromdate) AND (C.ST1_Date <= @todate) AND (A.Couses_Name = B.Reason_for_Cancellation))
AS Scheduled_No,
(SELECT C.ST1_Date
FROM DOS_TripsperKms_Cancelled AS B INNER JOIN
Daily_Operational_Statistics AS C ON B.DOS_Id = C.DOS_Id
WHERE (C.Depot_Id = @id) AND (C.ST1_Date > @fromdate) AND (C.ST1_Date <= @todate) AND (A.Couses_Name = B.Reason_for_Cancellation)) AS ST1_Date,
(SELECT C.Scheduled_Kms
FROM DOS_TripsperKms_Cancelled AS B INNER JOIN
Daily_Operational_Statistics AS C ON B.DOS_Id = C.DOS_Id
WHERE (C.Depot_Id = @id) AND (C.ST1_Date > @fromdate) AND (C.ST1_Date <= @todate) AND (A.Couses_Name = B.Reason_for_Cancellation))
AS Scheduled_Kms
FROM Couses_Master AS A
I want 1st coloum to be displayed completely next coloums data should be displayed in front of 1st col but only in respected rows if no match then tat cell in tat col should be blank or display zero

Member 10190046 6-Aug-13 4:38am

SELECT row_number()over (order by Author_name asc)as'SNo',
autdeta.unique_id,Author_name,phone,address,email,AuthRecordID, (select distinct Author_name from autdeta
)
FROM autdeta
inner join booksdeta
ON autdeta.unique_id = booksdeta.AuthRecordID
--order by autdeta.Author_name ASC
select * from autdeta

error shows blow
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

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

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us