Solved

sql nested select or join

Posted on 2014-10-30
3
357 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OSQL to execute sql command 26 26
Negative isnull? 3 16
SQL USE DATABASE VARIABLE 5 31
SQL Server Shrink hurting performance? 4 22
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

821 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