Solved

sql nested select or join

Posted on 2014-10-30
3
360 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 34

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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

691 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