not exists - help with multiple WHERE

Hi
I have the following sql

Select		a.dGPSDateTime 
                        ,a.vSequenceID
From	(select		top 5
		dGPSDateTime
	,	vSequenceID
from	tblCommonTrackingData
where	vReportID = 8
AND      CAST(vpkDeviceID as bigint)= 862193020453528
and		dGPSDateTime >= '2014-12-17 17:06:33.000'

--and	not exists
--		(select vpkDeviceID
--		from	tblTrip_Master
--		where	nEndEventInst = 913565)

order by dGPSDateTime) as a

Open in new window


This works, and returns the following
resut

I would like to un-comment the NOT EXISTS above to exclude the selected record
tblCommonTrackingData.vSequenceID is the same as
tblTrip_Master.nEndEventInst

However, when i uncomment it, i get no results at all
Im not sure NOT EXISTS is the best fit for this

basically, i dont want the row with 913565 to appear:
Where vSequenceID isn't 913565

Open in new window


However, they are 2 different tables so not sure why it isn't working
websssAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
However, when i uncomment it, i get no results at all
(..)basically, i dont want the row with 913565 to appear:
Well, if with the original query you get only 2 records and both with vSequenceID = 913565 it's natural that if you don't want it to appear then no records will be returned.

Anyway, you can add the criteria vSequenceID <> 913565 to the WHERE clause:
Select	a.dGPSDateTime,a.vSequenceID
From (select top 5
		dGPSDateTime,vSequenceID
	from tblCommonTrackingData
	where	vReportID = 8
	AND     CAST(vpkDeviceID as bigint)= 862193020453528
	and		dGPSDateTime >= '2014-12-17 17:06:33.000'
	and     vSequenceID <> 913565
	order by dGPSDateTime) as a

Open in new window

0
SimonCommented:
This should work as well...
Select		a.dGPSDateTime 
                        ,a.vSequenceID
From	(select		top 5
		dGPSDateTime
	,	vSequenceID
from	tblCommonTrackingData
where	vReportID = 8
AND      CAST(vpkDeviceID as bigint)= 862193020453528
and		dGPSDateTime >= '2014-12-17 17:06:33.000'

order by dGPSDateTime) as a

where a.vSequenceID <>913565
                                  

Open in new window

i.e. adding the where clause for the specific record you want to exclude to the main SELECT statement rather than the subquery. This would mean that you got a maximum of 4 results rather than 5 though, whereas Vitor's suggestion would still get you 5.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Simon, what's the difference between your solution and mine? The location of the filter?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

websssAuthor Commented:
the 2 sequence Id's are different in the image
0
SimonCommented:
@Vitor: Sorry, I hadn't refreshed the page. Yes the location of the filter is in the outer query and would limit the result set to max of 4 records rather than 5.
0
SimonCommented:
What didn't make sense to me was this bit...
--and      not exists
--            (select vpkDeviceID
--            from      tblTrip_Master
--            where      nEndEventInst = 913565)

because it is a different column name (nEndEventInst) that is being checked, rather than "vSequenceID", though I'm guessing that both refer to the same value?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
the 2 sequence Id's are different in the image
Yes, you are right. I went to double-check. So, just try to add the filter and see if you got the expected result.
0
websssAuthor Commented:
Thanks both
neither solution will work since the ID is dynamic and it needs to check if it exists in the other table

i.e.
--Where sequenceID not in
--            (select nEndEventInst
--            from      tblTrip_Master
--            where      nEndEventInst = 913565
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes the location of the filter is in the outer query and would limit the result set to max of 4 records rather than 5.
Simon, but then you are changing the logic of the query. If the idea is to get the TOP 5 than the filter should be inside the subselect.
0
websssAuthor Commented:
yes
nEndEventInst and vSequenceID are different names in both tables, but contain same value
0
websssAuthor Commented:
..actually, I only need TOP 1 since it should only exist once in this table
i was just using TOP 5 for testing sorry
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
neither solution will work since the ID is dynamic
If it's dynamic you need to work with a variable. In the code you posted is a constant. We can't guess if you don't provide us all the information.

This solution returns the TOP 5  records that not exists in TripMaster table:
Select	a.dGPSDateTime,a.vSequenceID
From (select top 5
		dGPSDateTime,vSequenceID
	from tblCommonTrackingData
	where	vReportID = 8
	AND     CAST(vpkDeviceID as bigint)= 862193020453528
	and		dGPSDateTime >= '2014-12-17 17:06:33.000'
	and     not exists
             (select vpkDeviceID
             from      tblTrip_Master 
             where     tblTrip_Master.nEndEventInst = tblCommonTrackingData)
	order by dGPSDateTime) as a

Open in new window

You need to find a way to add the variable for excluding the SequenceID that you want.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Forgot to add the SequenceID in the NOT EXISTS query. Also changed it for TOP 1:
Select	a.dGPSDateTime,a.vSequenceID
From (select top 1
		dGPSDateTime,vSequenceID
	from tblCommonTrackingData
	where	vReportID = 8
	AND     CAST(vpkDeviceID as bigint)= 862193020453528
	and		dGPSDateTime >= '2014-12-17 17:06:33.000'
	and     not exists
             (select vpkDeviceID
             from      tblTrip_Master 
             where     tblTrip_Master.nEndEventInst = tblCommonTrackingData.vSequenceID)
	order by dGPSDateTime) as a

Open in new window

0
SimonCommented:
You'll always get zero rows if both vSequenceIDs/nEndEventInsts are associated with the same vpkDeviceID in tblTrip_Master.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.