Solved

sql nested select or join

Posted on 2014-10-30
3
352 Views
Last Modified: 2014-11-02
Hi
I have the following code which runs and I see the results for a second,and then it errors with
Msg 512, Level 16, State 1, Line 82
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


 select dStartTime,dEndTime,iMaxSpeed,iDistance,iAverageSpeed,nStartEventInst,nEndEventInst,vpkDeviceID,
 (	
	Select vehiclePosition
	from tblTrackingData 
	where nEventInst = tm.nStartEventInst
) as startPos

 FROM tblTrip_Master tm where vpkDeviceID = '862170018835676'

Open in new window


The above Nested select gets the StartPosition, i also need to another nested select or join to get the End Position

Any help appreciated
0
Comment
Question by:websss
3 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40413359
select dStartTime,dEndTime,iMaxSpeed,iDistance,iAverageSpeed,nStartEventInst,nEndEventInst,vpkDeviceID,
FROM tblTrip_Master tm
INNER JOIN  (      
      SELECT nEventInst, MIN(vehiclePosition) AS StartPos ,MAX(vehiclePosition) AS LastPos
      from tblTrackingData
      
) as n
ON n.nEventInst = tm.nStartEventInst
WHERE  vpkDeviceID = '862170018835676'
0
 
LVL 22

Accepted Solution

by:
Ivo Stoykov earned 500 total points
ID: 40413368
Hi,

The error is obvious -
Subquery returned more than 1 value.
you have to add some where clause that limit it to one row or add to it SELECT TOP 1 * FROM [table]

select dStartTime,dEndTime,iMaxSpeed,iDistance,iAverageSpeed,nStartEventInst,nEndEventInst,vpkDeviceID,
 (	
	Select Top(1) vehiclePosition
	from tblTrackingData 
	where nEventInst = tm.nStartEventInst
) as startPos

 FROM tblTrip_Master tm where vpkDeviceID = '862170018835676'

Open in new window

HTH

Ivo Stoykov
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40413387
Start with a normal JOIN to get an idea of the data:

SELECT  TM.dStartTime ,
        TM.dEndTime ,
        TM.iMaxSpeed ,
        TM.iDistance ,
        TM.iAverageSpeed ,
        TM.nStartEventInst ,
        TM.nEndEventInst ,
        TM.vpkDeviceID ,
        TD.vehiclePosition
FROM    tblTrip_Master TM
        LEFT JOIN tblTrackingData TD ON TD.nEventInst = TM.nStartEventInst
WHERE   TM.vpkDeviceID = '862170018835676';

Open in new window


Your first query returned more than 1 row, thus your definition of "Start" seems to be unprecise or wrong. Maybe you need a further predicate in the JOIN condition á la TD.vpkDeviceID = TM.vpkDeviceID.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now